You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
PLAN Output (At least the detailed PLAN) should contain also the information from a selectable STORED PROCEDURE used in a statement.
Example DDL/DML to show issue:
--------------------------------------------------------------------------------
CREATE TABLE AUCTIONS
(
AUCTIONID BIGINT NOT NULL,
DESCRIPTION VARCHAR( 200) NOT NULL,
STARTPRICE NUMERIC( 12, 2) NOT NULL,
STEPPRICE NUMERIC( 8, 2),
CONSTRAINT PK_AUCTIONS PRIMARY KEY (AUCTIONID)
);
CREATE TABLE AUCTIONBIDS
(
AUCTIONBIDID BIGINT NOT NULL,
AUCTIONID BIGINT NOT NULL,
BIDPRICE NUMERIC( 12, 2) NOT NULL,
BIDDER VARCHAR( 100) NOT NULL,
BIDDATETIME TIMESTAMP NOT NULL,
CONSTRAINT PK_AUCTIONBIDS PRIMARY KEY (AUCTIONBIDID),
CONSTRAINT FK_AUCTIONBIDS_AUCTIONS FOREIGN KEY (AUCTIONID)
REFERENCES AUCTIONS (AUCTIONID)
ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE DESC INDEX I_AUCTIONBIDS_AUCTID_PRICE_DESC ON AUCTIONBIDS (AUCTIONID, BIDPRICE);
SET TERM ^^ ;
CREATE PROCEDURE P_GET_LASTBID (
AUCTIONID BigInt)
returns (
AUCTIONBIDID BigInt,
BIDDER VarChar(100),
BIDPRICE Numeric(12,2))
AS
BEGIN
FOR
SELECT
ab.AUCTIONBIDID, ab.BIDDER, ab.BIDPRICE
FROM
AUCTIONBIDS ab
WHERE
ab.AUCTIONID = :AUCTIONID
ORDER BY
ab.BIDPRICE DESC
FETCH FIRST 1 ROWS ONLY
INTO
:AUCTIONBIDID, :BIDDER, :BIDPRICE
DO
BEGIN
SUSPEND;
END
END ^^
SET TERM ; ^^
SELECT
*
FROM
AUCTIONS a
LEFT JOIN P_GET_LASTBID(a.AUCTIONID) ON (1 = 1)
Current (FB3.0 RC1) will output PLAN:
PLAN JOIN (A NATURAL, P_GET_LASTBID NATURAL)
Expected something like:
PLAN JOIN (A NATURAL, P_GET_LASTBID (PLAN (AB ORDER I_AUCTIONBIDS_AUCTID_PRICE_DESC)))
Current (FB3.0 RC1) will output detailed PLAN:
Select Expression
-> Nested Loop Join (outer)
-> Table "AUCTIONS" as "A" Full Scan
-> Procedure "P_GET_LASTBID" Scan
Expected something like :
Select Expression
-> Nested Loop Join (outer)
-> Table "AUCTIONS" as "A" Full Scan
-> Procedure "P_GET_LASTBID" Scan
Select Expression
-> First N Records
-> Filter
-> Table "AUCTIONBIDS" as "AB" Access By ID
-> Index "I_AUCTIONBIDS_AUCTID_PRICE_DESC" Range Scan (partial match: 1/2)
The text was updated successfully, but these errors were encountered:
This was "simplified" intentionally. At least in the legacy PLAN output, any non-trivial select expression makes the PLAN totally unreadable.
It's easier to represent in the explained output, but this would "explode" the output when many nested procedures are used.
Also, I believe it makes very little sense to print something that can be hardly analyzed. One cannot see what parts of the plan are to be executed, a procedure plan shows just all possible variations. So far the only known to me use for nested SP plans was to check it for NATURALs. A way better solution to find such planning bottlenecks is execute the query and check the runtime statistics for SEQ_READS.
Sean, just all internal execution plan (from the SP) output into the SP node.
And yes this can become very big, but now you need to guess what is used. Stripping out the query with all the parameters etc... is quite some work and you're no sure if the same plan will be choosen inside the SP.
What if it calls itself recursively.... oke, there are some things to think about :-)
I suppose that better will be providing command like
Explain Procedure "Procedure name"
or
Describe Procedure "Procedure name" instead of putting this in Explained Plan
After this command we can got explained plan for every statement inside procedure
with procedure line numer and column
Submitted by: @ArnoBrinkman
PLAN Output (At least the detailed PLAN) should contain also the information from a selectable STORED PROCEDURE used in a statement.
Example DDL/DML to show issue:
--------------------------------------------------------------------------------
CREATE TABLE AUCTIONS
(
AUCTIONID BIGINT NOT NULL,
DESCRIPTION VARCHAR( 200) NOT NULL,
STARTPRICE NUMERIC( 12, 2) NOT NULL,
STEPPRICE NUMERIC( 8, 2),
CONSTRAINT PK_AUCTIONS PRIMARY KEY (AUCTIONID)
);
CREATE TABLE AUCTIONBIDS
(
AUCTIONBIDID BIGINT NOT NULL,
AUCTIONID BIGINT NOT NULL,
BIDPRICE NUMERIC( 12, 2) NOT NULL,
BIDDER VARCHAR( 100) NOT NULL,
BIDDATETIME TIMESTAMP NOT NULL,
CONSTRAINT PK_AUCTIONBIDS PRIMARY KEY (AUCTIONBIDID),
CONSTRAINT FK_AUCTIONBIDS_AUCTIONS FOREIGN KEY (AUCTIONID)
REFERENCES AUCTIONS (AUCTIONID)
ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE DESC INDEX I_AUCTIONBIDS_AUCTID_PRICE_DESC ON AUCTIONBIDS (AUCTIONID, BIDPRICE);
SET TERM ^^ ;
CREATE PROCEDURE P_GET_LASTBID (
AUCTIONID BigInt)
returns (
AUCTIONBIDID BigInt,
BIDDER VarChar(100),
BIDPRICE Numeric(12,2))
AS
BEGIN
FOR
SELECT
ab.AUCTIONBIDID, ab.BIDDER, ab.BIDPRICE
FROM
AUCTIONBIDS ab
WHERE
ab.AUCTIONID = :AUCTIONID
ORDER BY
ab.BIDPRICE DESC
FETCH FIRST 1 ROWS ONLY
INTO
:AUCTIONBIDID, :BIDDER, :BIDPRICE
DO
BEGIN
SUSPEND;
END
END ^^
SET TERM ; ^^
COMMIT;
INSERT INTO AUCTIONS (AUCTIONID, DESCRIPTION, STARTPRICE, STEPPRICE) VALUES (1, 'Firebird SQL mascot', 80, 10);
INSERT INTO AUCTIONS (AUCTIONID, DESCRIPTION, STARTPRICE, STEPPRICE) VALUES (2, 'Firebird SQL QA collection', 50, 5);
INSERT INTO AUCTIONBIDS (AUCTIONBIDID, AUCTIONID, BIDPRICE, BIDDER, BIDDATETIME) VALUES (1, 2, 50, 'Cheapo', '11/25/2015 11:00:00.000');
INSERT INTO AUCTIONBIDS (AUCTIONBIDID, AUCTIONID, BIDPRICE, BIDDER, BIDDATETIME) VALUES (2, 2, 55, 'ICanDoBetter', '11/25/2015 11:00:24.000');
INSERT INTO AUCTIONBIDS (AUCTIONBIDID, AUCTIONID, BIDPRICE, BIDDER, BIDDATETIME) VALUES (3, 2, 75, 'IWantIt', '11/25/2015 11:01:07.000');
INSERT INTO AUCTIONBIDS (AUCTIONBIDID, AUCTIONID, BIDPRICE, BIDDER, BIDDATETIME) VALUES (4, 1, 100, 'IWantIt', '11/25/2015 11:14:30.000');
COMMIT;
--------------------------------------------------------------------------------
Running next query:
SELECT
*
FROM
AUCTIONS a
LEFT JOIN P_GET_LASTBID(a.AUCTIONID) ON (1 = 1)
Current (FB3.0 RC1) will output PLAN:
PLAN JOIN (A NATURAL, P_GET_LASTBID NATURAL)
Expected something like:
PLAN JOIN (A NATURAL, P_GET_LASTBID (PLAN (AB ORDER I_AUCTIONBIDS_AUCTID_PRICE_DESC)))
Current (FB3.0 RC1) will output detailed PLAN:
Select Expression
-> Nested Loop Join (outer)
-> Table "AUCTIONS" as "A" Full Scan
-> Procedure "P_GET_LASTBID" Scan
Expected something like :
Select Expression
-> Nested Loop Join (outer)
-> Table "AUCTIONS" as "A" Full Scan
-> Procedure "P_GET_LASTBID" Scan
Select Expression
-> First N Records
-> Filter
-> Table "AUCTIONBIDS" as "AB" Access By ID
-> Index "I_AUCTIONBIDS_AUCTID_PRICE_DESC" Range Scan (partial match: 1/2)
The text was updated successfully, but these errors were encountered: