--- In firebird-support@yahoogroups.com, "emb_blaster" <EMB_Blaster@...> wrote:

>
>
>
> --- In firebird-support@yahoogroups.com, "kokok_kokok" <kokok_kokok@> wrote:
> >
> > I get the same number of read records. It does not seem to improve the performance. You are right, my example is too simplified.
> >
> > My real T2 table is a complex query. It returns few records, but it reads thousands of records to provide the result. For this reason, I need to avoid that Firebird evaluates it for each T1 record.
> >
> > My problem is: I have 2 independent SQL consults. I need that Firebird returns me the two CTEs in only one result table.
> >
> > What I would do programmatically would be:
> >
> > 1) Calculate T1
> > 2) Calculate T2
> > 3) Loop in T1, joining a T2 row if T1.ID=T2.ID
> >
> >
> > In the above algorithm, I only do one query for T1, and one query for T2.
> > Using LEFT JOIN in firebird over two CTEs, I see that Firebird evaluates T2 for each T1 record.
> >
> > How can rewrite the SQL to get a join table over two independent sql consults without penalize the performance?
> >
> > Thank you
> >
>
> Maybe you should use stored procedures or cursors to do it "programmatically" instead... but I am not sure how it could improve your code. Seems you must avoid a simple FOR SELECT T1 DO {calc T2 joining T1}.
>
> So maybe cursors and new LEAVE clause (FB 2.1) would be the way to beat that...
>
> unfortunately i am not too good with cursors to help :(

you do this in mind to complicated ;-)
stored proc only is needed to first "table union" T1
or two to two streams if t2 will be in future also some union

SELECT * FROM

(
SELECT 1 AS VAL FROM rdb$database
UNION SELECT 2 FROM rdb$database
UNION SELECT 3 FROM rdb$database
UNION SELECT 4 FROM rdb$database
) T1
LEFT JOIN
(
SELECT 10 AS VAL FROM rdb$database

) T2
ON T1.VAL=T2.VA

pseudocode
Create procedure proc_T1
(
SELECT 1 AS VAL FROM rdb$database
UNION SELECT 2 FROM rdb$database
UNION SELECT 3 FROM rdb$database
UNION SELECT 4 FROM rdb$database
)


and then query

select * from
proc_T1 T1
LEFT JOIN
(
SELECT 10 AS VAL FROM rdb$database

) T2
ON T1.VAL=T2.VA

Karol Bieniaszewski