--- In firebird-support@yahoogroups.com, "karolbieniaszewski" <liviuslivius@...> wrote:

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

Hi Karol, :)

I'm not sure that a simple SP with Unions (ok, maybe with a For Select into) and then a left join will prevent firebird of do a fetch on T2 for every line on proc_T1.