Slow: > select t1.*, t2.* from > (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") t1 > left outer join > (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" order by "BJAR") t2 > on t1."BJAR"=t2."BJAR" > order by t1."TKEY",t2."TKEYT"
Quick: > select t1.*, t2.* from > (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") t1 > left outer join > (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") t2 > on t1."BJAR"=t2."BJAR" > order by t2."TKEY",t1."TKEYT" These are rather opposite queries, not giving the same result except if LEFT OUTER is used carelessly rather than thoughtfully. If you have no particular reason for using LEFT OUTER, just delete those two words everywhere and things would be much easier for the Firebird optimizer (in most cases, JOIN should be preferred over LEFT OUTER JOIN). LEFT OUTER JOIN is useful if you want to include a row even if there's no matching value for the RIGHT table. Your original query could be rewritten 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 My guess is that there's either no index for TL1_2.BJTX or that it is not selective and this makes it slow. The second query can be rewritten: 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_2 b1 left join BJTX b2 on b1.BJTX = b2.BJTX left join TL1_1 a1 on b2.BJAR = a1.BJAR left join BJAR a2 on a1.BJAR = a2.BJAR and BJAR is at least part of the PK for both TL1_1 and BJAR (which makes it fast). I would expect the same queries with JOIN rather than LEFT JOIN to be quick in both cases, since that allows the optimizer to select which table to access first in the plan. Just thought I should write this in case you're a (Firebird) SQL newbie, please apologize in case there is a good reason for all LEFT OUTERs. Set
