SELECT question...Interesting.

[Wokawidget]

I have 2 methods of doing a query. These are as follows:

SELECT BookingDetail.BookingKey, BookingDetail.BookedFrom, BookingDetail.BookedTo, 
COUNT(Catering.BookingKey) AS CateringCount, 
COUNT(Visitor.BookingKey) AS VisitorCount
FROM BookingDetail
LEFT OUTER JOIN Catering ON Catering.BookingKey = BookingDetail.BookingKey
LEFT OUTER JOIN Visitor ON Visitor.BookingKey = BookingDetail.BookingKey
GROUP BY BookingDetail.BookingKey, BookingDetail.BookedFrom, BookingDetail.BookedTo
HAVING COUNT(Catering.BookingKey) > 0

and

SELECT BookingDetail.BookingKey, BookingDetail.BookedFrom, BookingDetail.BookedTo, 
ISNULL(Catering.CateringCount, 0) AS CateringCount, 
ISNULL(Visitor.BookingKey, 0) AS VisitorCount
FROM BookingDetail
LEFT OUTER JOIN (SELECT BookingKey, COUNT(*) AS CateringCount FROM Catering GROUP BY BookingKey) Catering ON Catering.BookingKey = BookingDetail.BookingKey
LEFT OUTER JOIN (SELECT BookingKey, COUNT(*) AS VisitorCount FROM Visitor GROUP BY BookingKey) Visitor ON Visitor.BookingKey = BookingDetail.BookingKey
WHERE ISNULL(Catering.CateringCount, 0) > 0

Both these queries return EXACTLY the same result set.
But...which query is best?
Our DB team the 1st method, as the 2nd method does a sub query per row, which makes sense.
However, on a DB, with no indexs, I ran them.
The 1st query produced:

Table 'BookingDetail'. Scan count 1, logical reads 293, physical reads 0, read-ahead reads 0.
Table 'Catering'. Scan count 1, logical reads 21, physical reads 0, read-ahead reads 0.
Table 'Visitor'. Scan count 1, logical reads 10, physical reads 0, read-ahead reads 0.

SQL Server Execution Times:
CPU time = 468 ms, elapsed time = 2600 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

And the 2nd query, the one that was supposed to be slower, returns:

Table 'Visitor'. Scan count 1026, logical reads 10260, physical reads 0, read-ahead reads 0.
Table 'BookingDetail'. Scan count 1, logical reads 293, physical reads 0, read-ahead reads 0.
Table 'Catering'. Scan count 1, logical reads 21, physical reads 0, read-ahead reads 0.

SQL Server Execution Times:
CPU time = 406 ms, elapsed time = 410 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

As can see the 2nd query does shed loads of logical reads and scans compared to the 1st, but it's quicker...WAY quicker?! Why?

Woka

[Ecniv]

Ata guess the sub queries produce a smaller table/recordset internally which is then matched faster to the main first table.


Vince