Subject Re: [firebird-support] Re: Query that never ends (Firebird 2.5.3)
Author Carlos H. Cantu
Post date 2015-02-26T12:36:58Z
Re: [firebird-support] Re: Query that never ends (Firebird 2.5.3) As a workaround, sometimes you can get much better performance if you split your single query into multiple queries (kind of master-details) using a select stored procedure (or even an execute block).

For the main query, use a "for select" and inside its loop, you collect the data from the "left join" queries.

Good luck!

Carlos
Firebird Performance in Detail - http://videos.firebirddevelopersday.com
www.firebirdnews.org - www.FireBase.com.br


Hi Set;

Thank you. Yes, there is very important to use LEFT OUTER JOIN in the original query, as the first table is a tree, and the second one is a sub-tree with documents; and I need each row for the first tree, even when it has no documents (Like a tree of folders / subfolders / documents).

And yes, if I switch t1 and t2, it works very quick but it does not fit the needs (I have to rewrite all to work in this way).

But I really don't understand way the original query is working that slow; if I run t1:

select a1."BJAR",a1."TKEY",a2."OWNR",a2."TIPO",a2."DSCR" as "ADSCR" from "TL1_1" a1 left outer join "BJAR" a2 on a1."BJAR"=a2."BJAR"

It is really fast,  and if I run t2:

select b1."BJTX",b1."TKEYT",b2."OWNR" as "TOWNR",b2."BJAR",b2."DSCR" as "TDSCR",b2."VOCE",b2."RICH",b2."TEXT" from "TL1_2" b1 left outer join "BJTX"
b2 on b1."BJTX"=b2."BJTX"

It is really fast too, so I don't think it is a problem about TL1_2.BJTX as you suggest (I guess ...).

Now, why if Firebird can build both tables so quick, it will has problems to join both in that simple way?

Why do you think that TL1_2.BJTX can help?