> I don't understand why it takes so long, but if you told us what PLANs FB
> chose to use, we might gain some new insight (e.g. do a prepare in IB_SQL),

This may be where someting is wrong.

ROOMSTAT INDEX (RDB$PRIMARY3))(TRANSACTIONS INDEX
(RDB$PRIMARY8))(ROOMSTAT INDEX (RDB$PRIMARY3))(ROOMSTAT
INDEX (RDB$PRIMARY3))(TRANSACTIONS INDEX
(RDB$PRIMARY8))(ROOMSTAT INDEX (RDB$PRIMARY3))(TRANSACTIONS
INDEX (RDB$PRIMARY8))SORT ((T INDEX (RDB$PRIMARY8)))

Some brackets missing and no 'PLAN'?

So I went and aliased everthing again
-------------------------------------

SELECT T.ROOM
, COUNT (*) AS COUNTS
, MAX( T.PREVIOUS ) AS MAXTIME
, SUM( T.PREVIOUS ) AS SUMTIME
, ( SELECT COUNT (*) FROM TRANSACTIONS C1
WHERE C1.TICKET_REF BETWEEN :FROMD AND :TOD
AND C1.APPLET = T.APPLET
AND C1.PREVIOUS <
( SELECT R1.X5
FROM ROOMSTAT R1
WHERE R1.TERMINAL = T.ROOM )
AND C1.ROOM = T.ROOM ) AS CNT1
, ( SELECT COUNT (*) FROM TRANSACTIONS C2
WHERE C2.TICKET_REF BETWEEN :FROMD AND :TOD
AND C2.APPLET = T.APPLET
AND C2.PREVIOUS >=
( SELECT R2.X5
FROM ROOMSTAT R2
WHERE R2.TERMINAL = T.ROOM )
AND C2.PREVIOUS <
( SELECT R3.X6
FROM ROOMSTAT R3
WHERE R3.TERMINAL = T.ROOM )
AND C2.ROOM = T.ROOM ) AS CNT2
, ( SELECT COUNT (*) FROM TRANSACTIONS C3
WHERE C3.TICKET_REF BETWEEN :FROMD AND :TOD
AND C3.APPLET = T.APPLET
AND C3.PREVIOUS >=
( SELECT R4.X6
FROM ROOMSTAT R4
WHERE R4.TERMINAL = T.ROOM )
AND C3.ROOM = T.ROOM ) AS CNT3
, T.APPLET
FROM TRANSACTIONS T
WHERE T.TICKET_REF BETWEEN :FROMD AND :TOD
GROUP BY T.APPLET, T.ROOM
ORDER BY T.APPLET DESC, T.ROOM

and this time I got
-------------------

PLAN (R4 INDEX (RDB$PRIMARY3))
PLAN (C3 INDEX (RDB$PRIMARY8))
PLAN (R2 INDEX (RDB$PRIMARY3))
PLAN (R3 INDEX (RDB$PRIMARY3))
PLAN (C2 INDEX (RDB$PRIMARY8))
PLAN (R1 INDEX (RDB$PRIMARY3))
PLAN (C1 INDEX (RDB$PRIMARY8))
PLAN SORT ((T INDEX (RDB$PRIMARY8))))))

Still some missing brackets?

But it made no difference to the speed of operation, still
just as slow.

--
Lester Caine
-----------------------------
L.S.Caine Electronic Services