-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Svein Erling Tysvær
Sent: 20 January 2006 13:19
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: Slow SQL execution

> Plan
> PLAN SORT (JOIN (L NATURAL,M INDEX (CARD_TYPE_IDX1),D INDEX
> (RDB$PRIMARY114,CD$$__ABB__DETAIL_8_IDX2,RULLOUT_CODE_IDX1,
> I_SBN_CD$$__ABB__DETAIL_8,SUB_BATCH_NO_IDX1,
> I_SBN_CD$$__ABB__DETAIL_8, SUB_BATCH_NO_IDX1,
> I_SBN_CD$$__ABB__DETAIL_8,SUB_BATCH_NO_IDX1,
> CD$$__ABB__DETAIL_8INDEX1,CD$$__ABB__DETAIL_8INDEX1)))

Ouch, what a horrible plan! You will get some improvement simply by
adding ((...) OR 2=0), where ... is everything in your WHERE clause
that refers to D, excepting the fields that are in RDB$PRIMARY114.

Though it depends a lot upon the number of records in L whether this
is a good plan or not. Normally, you'd rather try to make D the first
table in your plan since this is where you have your real WHERE criteria.

You could - e.g. - do

select 'U'||D.Batch_No || '-' || D.Sub_Batch_No as Batch_No,
d.Batch_No as FBatch_No,
d.Sub_Batch_No as FSub_Batch_No, L.Display_Name,
L.C_Centre,d.reasoncode,
count(d.sub_batch_no) as carrier_count
from cd$$__abb__master_8 M
join Livery L ON M.Card_Type||'' = L.CardType
JOIN cd$$__abb__detail_8 D ON M.Batch_No = D.Batch_No+0
where d.carrier_required = 'T'
and d.pullout_code = 0
and d.runtype in ('NEW/REISSUE','REPLACEMENT')
and ((m.Batch_No = 7 and d.Sub_Batch_No between 1 and 2)
or (m.Batch_No = 8 and d.Sub_Batch_No = 1))
group by
D.Batch_No,D.Sub_Batch_No,L.Display_Name,d.reasoncode,l.C_Centre
order by d.Batch_No, d.Sub_Batch_No , L.Display_Name

But you still have to know a lot about your data to get the very best
plan. Are there many records with pullout_code = 0? If so, change to
pullout_code+0 = 0 to avoid this index. And similarly for runtype,
Batch_No and Sub_Batch_No. You may even consider a combined index on
Batch_No and Sub_Batch_No if these are commonly used together (but
then you also have to remove the existing index for whichever field is
first in this combined index).

My suspected diagnosis of your problem, so far: Excessive use of
indexes combined with stupid ordering of tables in the plan by the
optimizer.

HTH,
Set


Firstly, thanks (all you guys) for the help so far. It's much appreciated!
Your rewritten query cuts execution time by about a quarter. Still some way to cutting some more time from this thing. Will look at advice in this post to see if anything else can be cut at this stage.



Kevin.