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

--- In firebird-support@yahoogroups.com, "Brandon, Kevin" wrote:
> Hi all,
>
> Some information that may help you to help me:
> Firebird version: 1.0 (upgrading this version is not a viable
> option at the moment)
> Page Size: 4096
> SQL Dialect: 1
> Sweep Interval: 2000
> ODS version: 10.1
> Buffer | Pages 2048
>
> The problem: Executing the following SQL on a DELL power Edge
> server with 1Gb of ram is taking up-to 2 hours to execute. As you
> can see the SQL itself is not that complicated and on the surface of
> it should not take as long as it does to execute. Give that
> ssumption, and after ensuring that there are sufficient indexes on
> the tables and fields involved in producing the results . I am at
> something of a lost, as to where to turn next except for upgrading
> to a later version of the software (which is not currently an option
> available to me)
>
> Any help you can provide leading to a solution of a better
> understanding of the problem would be greatly appreciated. Time is
> critical on this one but, I suppose that time is critical for
> everyone that email you with their dull Firebird/SQL problem.
>
> Again, if you can help in any way it would be much appreciated. I
> have tried to provide as much information as possible, if you
> required any more, please don't hesitate in posting a reply.