SELECT question...Interesting.
I have 2 methods of doing a query. These are as follows:
Code:
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
Code:
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:
Quote:
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:
Quote:
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? :confused:
Woka
Re: SELECT question...Interesting.
Ata guess the sub queries produce a smaller table/recordset internally which is then matched faster to the main first table.
Vince
