I have an interesting little problem relating to generating
information for a report.

I am looking to produce some ten records from 5 to 10
thousand.

Each has the statistics for a queue and has three 'Counts' -
tickets waiting less than x, tickets waiting between x and
y, ticket waiting over y.

A single query for each of these is almost instant, and I
have a set of four linked queries that produce the data in
seconds.

What I thought would be tidier would be to put it all into
one query - which works fine - but takes hours instead of
seconds!

The 'slow' query is as follows:-

SELECT T.ROOM
, COUNT( * ) AS COUNTTOTAL
, MAX( PREVIOUS ) AS MAXIMUM
// Less than x
, ( SELECT COUNT (*) FROM TRANSACTIONS
WHERE TICKET_REF BETWEEN :FROMD AND :TOD
AND APPLET = T.APPLET AND ROOM = T.ROOM
AND PREVIOUS < ( SELECT X FROM ROOMSTAT WHERE TAG =
T.ROOM )
)
AS CNT1
// Between x and y
, ( SELECT COUNT (*) FROM TRANSACTIONS
WHERE TICKET_REF BETWEEN :FROMD AND :TOD
AND APPLET = T.APPLET AND ROOM = T.ROOM
AND PREVIOUS >= ( SELECT X FROM ROOMSTAT WHERE TAG =
T.ROOM )
AND PREVIOUS < ( SELECT Y FROM ROOMSTAT WHERE TAG =
T.ROOM )
)
AS CNT2
// Greater than y
, ( SELECT COUNT (*) FROM TRANSACTIONS
WHERE TICKET_REF BETWEEN :FROMD AND :TOD
AND APPLET = T.APPLET AND ROOM = T.ROOM
AND PREVIOUS >= ( SELECT X FROM ROOMSTAT WHERE TAG =
T.ROOM )
AND PREVIOUS < ( SELECT Y FROM ROOMSTAT WHERE TAG =
T.ROOM )
)
AS CNT3
, T.APPLET
FROM TRANSACTIONS T
WHERE TICKET_REF BETWEEN :FROMD AND :TOD
GROUP BY T.APPLET DESC, T.ROOM
ORDER BY T.APPLET, T.ROOM

I just plug in the dates for the report as :FROMD and :TOD.

The fast version just has CNT1, 2 and 3 queries separate and
linked by parameters to the base query ( T.APPLET and T.ROOM
).

The only 'SORT' in the plan is the final ORDER BY which has
5 lines in the one that has just take 50 minutes to process.

The solution I have is fine, it is just a question of why
should combining these simple operations cause such a
slowdown?

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