Subject Re: [firebird-support] Stored Procedures Execution Plan
Author João Paulo Carvalho
Post date 2016-06-01T12:24:17Z
Hi.


I've been trying to skim over some possibilities to investigate possible limitations. Here it follow my results and test cases:

- If you call Stored Procedures (SP's) with EXECUTE PROCEDURE statements, plans are not available;
- If you call SP's via SELECT FROM, you got expected statement plans, if this SP in turn DOESN'T call any other SP;
- If you call SP's via SELECT FROM, you got partially expected statement plans, if this SP in turn CALL any other SP. In this case you won't get called SP plans, and if you exec called SP's with EXECUTE PROCEDURE, even the execution of called SP's will be omitted;


This is the test case script I used (executed with FlameRobin):

    --
    -- CREATING OBJECTS
    --
RECREATE TABLE test (c1 INT, c2 INT, c3 INT);
CREATE INDEX test_ix1 ON test (c1);
CREATE INDEX test_ix2 ON test (c2);


SET TERM ^ ;
RECREATE PROCEDURE test_01_selectable_sub_sp (c_cons INT)
RETURNS (c_ret INT)
AS
BEGIN
    SELECT c1
    FROM test
    WHERE c1 = :c_cons
    INTO :c_ret;

    SELECT c1
    FROM test
    WHERE c2 = :c_ret
    INTO :c_ret;

    SELECT c1
    FROM test
    WHERE c3 = :c_ret
    INTO :c_ret;
    
    SUSPEND;
END^
SET TERM ; ^



SET TERM ^ ;
RECREATE PROCEDURE test_03_selectable_sp (c_cons INT)
RETURNS (c_ret INT)
AS
BEGIN
    SELECT c_ret
    FROM test_01_selectable_sub_sp(:c_cons)
    INTO :c_ret;

    SELECT c_ret
    FROM test_01_selectable_sub_sp(:c_ret)
    INTO :c_ret;
    
    SUSPEND;
END^
SET TERM ; ^


SET TERM ^ ;
RECREATE PROCEDURE test_04_selectable_sp (c_cons INT)
RETURNS (c_ret INT)
AS
BEGIN
    EXECUTE PROCEDURE test_01_selectable_sub_sp(:c_cons)
        RETURNING_VALUES (:c_ret);

    EXECUTE PROCEDURE test_01_selectable_sub_sp(:c_ret)
        RETURNING_VALUES (:c_ret);
    
    SUSPEND;
END^
SET TERM ; ^




    --
    -- EXECUTING SP'S
    --

EXECUTE PROCEDURE test_01_selectable_sub_sp(1);
EXECUTE PROCEDURE test_03_selectable_sp(1);
EXECUTE PROCEDURE test_04_selectable_sp(1);
-- Got:
--      Plan not available.


SELECT * FROM test_01_selectable_sub_sp(1);
-- Expected (something like):
--    PLAN (test INDEX (test_ix1))(test INDEX (test_ix2))(test NATURAL)
--
-- Got:
--    PLAN (TEST INDEX (TEST_IX1))(TEST INDEX (TEST_IX2))(TEST NATURAL)
--
-- Analysis:
--    Ok

SELECT * FROM test_03_selectable_sp(1);
-- Expected (something like):
--    PLAN 
--         (test_01_selectable_sub_sp NATURAL)
--                  (test INDEX (test_ix1))(test INDEX (test_ix2))(test NATURAL)
--         (test_01_selectable_sub_sp NATURAL)
--                  (test INDEX (test_ix1))(test INDEX (test_ix2))(test NATURAL)
--
-- Got:
--    PLAN (TEST_01_SELECTABLE_SUB_SP NATURAL)(TEST_01_SELECTABLE_SUB_SP NATURAL)
--
-- Analysis:
--    Missing called stored procedure plans


SELECT * FROM test_04_selectable_sp(1);
-- Expected (something like):
--    PLAN 
--         (test_01_selectable_sub_sp NATURAL)
--                  (test INDEX (test_ix1))(test INDEX (test_ix2))(test NATURAL)
--         (test_01_selectable_sub_sp NATURAL)
--                  (test INDEX (test_ix1))(test INDEX (test_ix2))(test NATURAL)
--
-- Got:
--    PLAN (TEST_04_SELECTABLE_SP NATURAL)
--
-- Analysis:
--    Missing called stored procedure plans and indication of called stored procedures execution 



Att.
João Paulo


DE: "Gabriel Frones grfrones@... [firebird-support]" <firebird-support@yahoogroups.com>
Para: firebird-support@yahoogroups.com
Enviadas: Terça-feira, 31, Maio 2016 14:39:56
Assunto: Re: [firebird-support] Stored Procedures Execution Plan

 
It was sent and received... but this is working for me out of the box in FB 2.5.4. I just write the select * from SP and ask for plan and it gives me the plan for the inside statements. Haven't tested on non-selectable SPs.

Em qua, 25 de mai de 2016 às 08:20, João Paulo Carvalho joao_paulo_c@... [firebird-support] <firebird-support@yahoogroups.com> escreveu:
 
Hi.


Since this was my first post in this list, I'm not sure if it was sent.

If you already get it, or this is not the right place to discuss this kind of topic, I would be very grateful if you tell me where is.

Att.
João Paulo


DE: "joao_paulo_c@... [firebird-support]" <firebird-support@yahoogroups.com>
Para: firebird-support@yahoogroups.com
Enviadas: Sexta-feira, 20, Maio 2016 15:54:07
Assunto: [firebird-support] Stored Procedures Execution Plan

 
Hi.


I would like to know if it is possible to display the plan of stored procedure statements.

I'm interested in the plan of the statements inside the stored procedure (SP), not the plan just informing that I'm executing an SP.

We are running also selectable SP's.

Att.
João Paulo