> I cannot answer your question, but wonder what your PLANs are -
> particularly for the subselects. And does it make any difference if you
> extend your use of use of aliases, i.e.

All simple indexes - adding extra aliases makes no
difference

> AND T3.PREVIOUS >= ( SELECT R4.X FROM ROOMSTAT R4 WHERE R4.TAG =
> T.ROOM )
> //This is probably a copying mistake? AND PREVIOUS < ( SELECT Y FROM
> ROOMSTAT WHERE TAG = T.ROOM )

Yes this should have been
AND T3.PREVIOUS >= ( SELECT R4.Y FROM ROOMSTAT R4 WHERE
R4.TAG = T.ROOM )

> Roomstat.tag is something you want an index on (of course), and probably
> also a compound index on (transactions.room, transactions.applet,
> transactions.ticket_ref - important to have transaction.ticket_ref behind
> the other fields of this index). I don't quite understand why you use >=
> when saying it is greater than (you only use < for less than), but the
> logic behind everything is all yours.

Why would I want a compound index?
ROOMSTAT.TAG is Indexed along with TICKET_REF, but no
compound indexes.

SELECT COUNT (*) FROM TRANSACTIONS
WHERE TICKET_REF BETWEEN :FROMD AND :TOD
AND APPLET = :APPLET AND ROOM = :ROOM
AND PREVIOUS < ( SELECT X FROM ROOMSTAT WHERE TAG = :ROOM )

Gives it's results in sub second time. Calling it ( and the
matching two ) 5 times gives me the information in seconds.
It is only when it gets put together in one query it takes
hours,

( >= is correct - Times are in minutes - want less the x and
greater then or equal to x. This should not make any
difference. )
--
Lester Caine
-----------------------------
L.S.Caine Electronic Services