| Subject | Huge performance different from FB 2.5.2 vs FB 3.02 |
|---|---|
| Author | |
| Post date | 2017-09-22T09:48:53Z |
Hello guys,
I have this Query
select c.ID , e.CD, f.CD, sum(a.STCK_CLC) as qty, e.UNT, d.ID, g.ID
from t_in_ot2 a, t_in_ot1 b, M_FNGD c, M_ART d, M_Sz e, M_ASST f, M_CLR g, m_trs_typ h, G_TMP j
where d.ID = c.ART_ID
and g.ID = c.CLR_ID
and e.ID = c.SZ_ID
and f.ID = c.ASST_ID
and c.id = a.id_gd
and b.id = a.id_in_ot1
and b.id_trs_typ = h.ID
and h.STCK_CLC is not NULL
and h.STCK_TYP = 'F'
and d.TYP = 'F'
and b.is_cls = 'F'
and b.trs_dt BETWEEN '12/31/11' and '09-18-17'
and b.id_div_lc = j.ID
and e.UNT in('PSG','CT')
and b.CHCK_BY is not null
and b.cncl_by is null
and a.IS_VW = 'Y'
and f.ID = c.ASST_ID
and g.ID = c.CLR_ID
GROUP by c.ID, e.cd, f.cd, e.UNT, d.ID, g.ID
On SuperClasic FB 2.5.2 on Win 7 64, it took about 48 sec, here is the PLAN :
PLAN 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)))
Executing...
Done.
70013857 fetches, 20 marks, 235849 reads, 27 writes.
0 inserts, 0 updates, 0 deletes, 17905601 index, 8120 seq.
Delta memory: 74656432 bytes.
Total execution time: 48.672s
Script execution finished.
Looks like FB 3.0.2 Query PLAN not as good as FB 2.5.2.
How to make sure that Query PLAN from FB 3.0.2 is the optimum one? Bad plan affect performance badly.
Thanks & regards,
I have this Query
select c.ID , e.CD, f.CD, sum(a.STCK_CLC) as qty, e.UNT, d.ID, g.ID
from t_in_ot2 a, t_in_ot1 b, M_FNGD c, M_ART d, M_Sz e, M_ASST f, M_CLR g, m_trs_typ h, G_TMP j
where d.ID = c.ART_ID
and g.ID = c.CLR_ID
and e.ID = c.SZ_ID
and f.ID = c.ASST_ID
and c.id = a.id_gd
and b.id = a.id_in_ot1
and b.id_trs_typ = h.ID
and h.STCK_CLC is not NULL
and h.STCK_TYP = 'F'
and d.TYP = 'F'
and b.is_cls = 'F'
and b.trs_dt BETWEEN '12/31/11' and '09-18-17'
and b.id_div_lc = j.ID
and e.UNT in('PSG','CT')
and b.CHCK_BY is not null
and b.cncl_by is null
and a.IS_VW = 'Y'
and f.ID = c.ASST_ID
and g.ID = c.CLR_ID
GROUP by c.ID, e.cd, f.cd, e.UNT, d.ID, g.ID
On SuperClasic FB 2.5.2 on Win 7 64, it took about 48 sec, here is the PLAN :
PLAN 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)))
Executing...
Done.
70013857 fetches, 20 marks, 235849 reads, 27 writes.
0 inserts, 0 updates, 0 deletes, 17905601 index, 8120 seq.
Delta memory: 74656432 bytes.
Total execution time: 48.672s
Script execution finished.
On SuperServer FB 3.0.2 on Win 7 64, it took about 4.5 minutes, here is the PLAN
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 ! This is because FB 3.0.2 took different PLAN from FB 2.5.2.
When PLAN from FB 2.5.2 applied to FB 3.0.2, execution time almost the same.Looks like FB 3.0.2 Query PLAN not as good as FB 2.5.2.
How to make sure that Query PLAN from FB 3.0.2 is the optimum one? Bad plan affect performance badly.
Thanks & regards,
Anto.