Hi,
this is terible to read. Please change join style to explicite join style
instead SELECT .. FROM TABLE_A A, TABLE_B B WHERE A.ID=B.ID
write SELECT .. FROM TABLE_A A INNER JOIN TABLE_B B ON A.ID=B.ID
regards,
Karol Bieniaszewski
From: trsk...@yahoo.com [firebird-support]
Sent: Friday, September 22, 2017 11:48 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Huge performance different from FB 2.5.2 vs FB 3.02
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
an d 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.
