Join procedures only once on Firebird

Question

I am trying to leave two stored procedures in a Firebird request. In my example, the data first returns 70 records, the second only 1 record.

select
    --...
from MYSP1('ABC', 123) s1
    left join MYSP2('DEF', 456) s2
         on s1.FIELDA = s2.FIELDA
        and s1.FIELDB = s2.FIELDB

The problem is performance: it takes 10 seconds, and each procedure takes less than 1 second. I suspect that the procedures are performed several times, and not once. It would be wise to execute them only once, because I pass them fixed parameters.

Is there a way to get Firebird to simply execute each procedure once and then join their results?

Answer 1

, , , MYSP2 MYSP1 .

:

create global temporary table MY_TEMP_TABLE
(
    FIELDA varchar(3) not null,
    FIELDB smallint not null,
    FIELDC varchar(10) not null
 );

:

--cache MYSP2 results
delete from MY_TEMP_TABLE;
insert into MY_TEMP_TABLE
    select *
    from MYSP2('DEF', 456)
    ;

--join data
for
select
    --...
from MYSP1('ABC', 123) s1
    left join MY_TEMP_TABLE s2
         on s1.FIELDA = s2.FIELDA
        and s1.FIELDB = s2.FIELDB
into
    --...
do
    suspend;

, !

Answer 2

, :

with MYSP2W as (MYSP2('DEF', 456))
select
    --...
from MYSP1('ABC', 123) s1
    left join MYSP2W s2
    on s1.FIELDA = s2.FIELDA
    and s1.FIELDB = s2.FIELDB