Hello guys,
I have this query :
SELECT a.BB, a.PLUS, a.MINUS, a.ENDBLNC, vw.UNT,vw.SLS_PRC_DS,vw.SLS_PRC_DT
from GET_STCK_MUT_PSG_CNT('09-18-17','09-18-17','All') a, M_FNGD_VW vw
where a.ID = vw.FNGD_ID
and a.ENDBLNC >= 0
order by vw.NM,vw.cl, vw.SZ_CD, vw.ASST_CD
On SuperClasic FB 2.52 on Win 7 64, it took +/- 50 secs :
PLAN SORT (JOIN (JOIN (JOIN (JOIN (G_T_IN_OT3 NATURAL, A INDEX (IDX_M_FNGD3)),
A INDEX (IDX_M_FNGD3), SORT (JOIN (H NATURAL, B INDEX (IDX_T_IN_OT11), J INDEX
(IDX_G_TMP23), A INDEX (T_IN_OT2_T_IN_OT1), C INDEX (RDB$PRIMARY25), D INDEX
(RDB$PRIMARY21), E INDEX (RDB$PRIMARY24), G INDEX (RDB$PRIMARY22), F INDEX
(RDB$PRIMARY23))), A INDEX (IDX_M_FNGD3), A INDEX (IDX_M_FNGD3), SORT (JOIN (H
NATURAL, B INDEX (IDX_T_IN_OT11), J INDEX (IDX_G_TMP23), A INDEX
(T_IN_OT2_T_IN_OT1), C INDEX (RDB$PRIMARY25), D INDEX (RDB$PRIMARY21), E INDEX
(RDB$PRIMARY24), G INDEX (RDB$PRIMARY22), F INDEX (RDB$PRIMARY23)))), A INDEX
(IDX_M_FNGD3), A INDEX (IDX_M_FNGD3), SORT (JOIN (H NATURAL, B INDEX
(IDX_T_IN_OT11), J INDEX (IDX_G_TMP23), A INDEX (T_IN_OT2_T_IN_OT1), C INDEX
(RDB$PRIMARY25), D INDEX (RDB$PRIMARY21), E INDEX (RDB$PRIMARY24), G INDEX
(RDB$PRIMARY22), F INDEX (RDB$PRIMARY23)))), SORT (G_T_IN_OT3 NATURAL))(VW A
INDEX (RDB$PRIMARY25))(VW C INDEX (RDB$PRIMARY28))(VW D INDEX
(RDB$PRIMARY21))(VW E INDEX (RDB$PRIMARY22))(VW F INDEX (RDB$PRIMARY24))(VW J
INDEX (RDB$PRIMARY23))(VW B INDEX (RDB$PRIMARY27))(VW H INDEX
(RDB$PRIMARY47))(VW I INDEX (RDB$PRIMARY48))(VW G INDEX (RDB$PRIMARY26)))
Executing...
Done.
70901350 fetches, 52178 marks, 236349 reads, 19 writes.
25577 inserts, 0 updates, 0 deletes, 18067187 index, 33745 seq.
Delta memory: 19474744 bytes.
G_T_IN_OT3: 25577 inserts.
Total execution time: 49.882s
Script execution finished.
On SuperServer FB 3.02, it took about 4.5 minutes :
PLAN SORT (JOIN (JOIN (JOIN (JOIN (A NATURAL, VW A INDEX (RDB$PRIMARY25)), VW
C INDEX (RDB$PRIMARY28), VW D INDEX (RDB$PRIMARY21), VW E INDEX
(RDB$PRIMARY22), VW F INDEX (RDB$PRIMARY24), VW J INDEX (RDB$PRIMARY23)), VW B
INDEX (RDB$PRIMARY27), VW H INDEX (RDB$PRIMARY47), VW I INDEX (RDB$PRIMARY48)),
VW G INDEX (RDB$PRIMARY26)))
Executing...
Done.
290144826 fetches, 51085 marks, 756444 reads, 15 writes.
25187 inserts, 0 updates, 0 deletes, 93504000 index, 25607 seq.
Delta memory: 21251608 bytes.
G_T_IN_OT3: 25187 inserts.
Total execution time: 0:04:31 (hh:mm:ss)
Script execution finished.
Huge different. Any idea how to fix this on FB 3.02 ?
Thanks & regards,
Anto
