>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?
Your plan: PLAN SORT (JOIN (JOIN (T1 A1 NATURAL, T1 A2 INDEX (BJAR_PK)), JOIN (T2 B1 NATURAL, T2 B2 INDEX (BJTX_PK)))) contains NATURAL*2. The first is OK (without a WHERE clause you have to scan one table fully), the second makes things very slow (I think of it as B1 having to be scanned naturally for each potential row from A1 and A2). It's like me being able to greet 100 people quite quickly and also walk 100 meters in not all too long time, but if I had to walk 100 meters between each person I greeted, it would take me very long... >Why do you think that TL1_2.BJTX can help? Your original SQL was written in a manner that was very different to how I normally write queries, but after rewriting it in my preferred style: select a1.BJAR, a1.TKEY, a2.OWNR, a2.TIPO, a2.DSCR ADSCR, b1.BJTX, b1.TKEYT, b2.OWNR TOWNR, b2.BJAR, b2.DSCR TDSCR, b2.VOCE, b2.RICH, b2.TEXT from TL1_1 a1 left join BJAR a2 on a1.BJAR = a2.BJAR left join BJTX b2 on a1.BJAR = b2.BJAR left join TL1_2 b1 on b2.BJTX = b1.BJTX With your query written this way, I'd say it's easy to see that an index on TL1_2.BJTX could speed up the query considerably if the field is reasonably selective. Your original query could not use such a query (despite the result being the same), since you through having FROM TL1_2 LEFT JOIN BJTX explicitly tell the optimizer to access TL1_2 before BJTX. This particular LEFT JOIN is redundant, since you then join the result with the other query on the RIGHT table (on t1.BJAR=t2.BJAR). If you already have an index on TL1_2.BJTX, then you may experience a speed increase simply by changing FROM TL1_2 LEFT JOIN BJTX to FROM TL1_2 JOIN BJTX. HTH, Set
