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.

Anto.