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