Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Add PLAN used by SELECTABLE STORED PROCEDURE also into PLAN where it used [CORE5035] #5322

Open
firebird-automations opened this issue Nov 25, 2015 · 5 comments

Comments

@firebird-automations
Copy link
Collaborator

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)

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: @ArnoBrinkman

I can agree that for the legacy PLAN output it could become big and unreadable.

IMO for the detailed execution PLAN it doesn't matter that it becomes huge, as long as i get all needed information, that's way more important.

@firebird-automations
Copy link
Collaborator Author

Commented by: Sean Leyne (seanleyne)

Arno,

How would you propose that selectable procedures with complex/multiple queries be represented?

@firebird-automations
Copy link
Collaborator Author

Commented by: @ArnoBrinkman

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 :-)

@firebird-automations
Copy link
Collaborator Author

Commented by: @livius2

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants