[firebird-support] Re: Query that never ends (Firebird 2.5.3)
25.02.2015 22:38, martin_gorr...@yahoo.es wrote: I have this SQL: 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 Please show us the query plan. BTW, I don't think you really need ORDER BY inside t2. Dmitry
[firebird-support] Re: Query that never ends (Firebird 2.5.3)
25.02.2015 23:29, martin_gorr...@yahoo.es wrote: 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) t2 on t1.BJAR=t2.BJAR order by t1.TKEY,t2.TKEYT PLAN SORT (JOIN (JOIN (T1 A1 NATURAL, T1 A2 INDEX (BJAR_PK)), JOIN (T2 B1 NATURAL, T2 B2 INDEX (BJTX_PK Really bad case for Firebird, as this query cannot use indices for t1 join t2 and hash/merge outer joins are not supported. As a result, you get an interative re-execution of t2 for the every row in t1. Sigh. Dmitry
[firebird-support] Re: Query that never ends (Firebird 2.5.3)
Hi Dmitry, Thank you. I agree with you about ORDER BY inside t2, my original code does not have it, and I added it trying to find a solution. Here is what you asked for: Starting transaction... Preparing query: 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) t2 on t1.BJAR=t2.BJAR order by t1.TKEY,t2.TKEYT Prepare time: 0.015s Field #01: TL1_1.BJAR Alias:BJAR Type:STRING(10) Field #02: TL1_1.TKEY Alias:TKEY Type:STRING(80) Field #03: BJAR.OWNR Alias:OWNR Type:STRING(10) Field #04: BJAR.TIPO Alias:TIPO Type:STRING(1) Field #05: BJAR.DSCR Alias:ADSCR Type:STRING(200) Field #06: TL1_2.BJTX Alias:BJTX Type:STRING(10) Field #07: TL1_2.TKEYT Alias:TKEYT Type:STRING(20) Field #08: BJTX.OWNR Alias:TOWNR Type:STRING(10) Field #09: BJTX.BJAR Alias:BJAR Type:STRING(10) Field #10: BJTX.DSCR Alias:TDSCR Type:STRING(400) Field #11: BJTX.VOCE Alias:VOCE Type:STRING(400) Field #12: BJTX.RICH Alias:RICH Type:STRING(1) Field #13: BJTX.TEXT Alias:TEXT Type:BLOB SUB_TYPE 0 PLAN SORT (JOIN (JOIN (T1 A1 NATURAL, T1 A2 INDEX (BJAR_PK)), JOIN (T2 B1 NATURAL, T2 B2 INDEX (BJTX_PK Script execution finished.
