Join procedures only once on Firebird

user_505893 0
Viewed 602

I'm trying to left join two stored procedures in a Firebird query. In my example data the first returns 70 records, the second just 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 performances: it takes 10 seconds, while each procedure takes less than 1 second. I suspect that procedures are run multiple times instead of just once. It would make sense to execute them just once, because I pass fixed parameters to them.

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

firebird join stored-procedures
2 Answers

Since it seems there is no way, I solved this issue running this query inside a new stored procedure, where I cache all results from MYSP2 into a global temporary table and make the join between MYSP1 and the temporary table.

This is temporary table definition:

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

This is stored procedure body:

--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;

But if there is another solution without temporary tables it would be great!

user_5058930

Maybe this can help:

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
user_71103010
Related