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

Ouch, SQL-89! Let's change to SQL-92 so that I understand more of it:

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
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

We do lack information about the plan and index statistics, you say
you have 'sufficient' indexes, but are you aware that excessive
indexes may be as bad as too few indexes? And that COUNT is very time
consuming if it has to count lots of records?

Indexes on L.CardType and M.Batch_No is probably required, and then
one or two of D.carrier_required, D.pulloutcode, D.runtype or
D.Sub_Batch_No, but it is difficult to tell which one without knowing
anything about the data and number of records in each table.

HTH,
Set



There are over 1.7 million records in this database. Using the free version of IB Expert, it doesn't allow me to view the execution plan. Is there a way of viewing this maybe from a dos prompt.

Kevin