Which of these queries are best for performance ? Sometimes I wonder if SHORT scripts really is the best thing to focus on. These scripts performs the same task. With left joins i can achieve what i want with just a few lines. But then I tried with a longer script, using unions. Which is the best method? Between this:
SELECT p.productID, p.product, C.color, C.colorID, S.size, S.sizeID, q.qty From quantities q
INNER join products P ON p.productID = q.productID
LEFT JOIN colors C ON C.colorID = q.colorID
LEFT JOIN sizes S ON S.sizeID = q.sizeID
--WHERE q.productID = @productID
and this:
SELECT p.productID, p.product, C.color, C.colorID, S.size, S.sizeID, q.qty From quantities q
inner join products P ON p.productID = q.productID
INNER JOIN colors C ON C.colorID = q.colorID
INNER JOIN sizes S ON S.sizeID = q.sizeID
--WHERE q.productID = @productID
UNION
SELECT p.productID, p.product, NULL, NULL, S.size, S.sizeID, q.qty From quantities q
inner join products P ON p.productID = q.productID
INNER JOIN sizes S ON S.sizeID = q.sizeID
WHERE /* q.productID = @productID AND */ q.sizeID IS NOT NULL AND q.colorID IS NULL
UNION
SELECT p.productID, p.product, C.color, C.colorID, NULL, NULL, q.qty From quantities q
inner join products P ON p.productID = q.productID
INNER JOIN colors C ON C.colorID = q.colorID
WHERE /* q.productID = @productID AND */ q.colorID IS NOT NULL AND q.sizeID IS NULL
UNION
SELECT p.productID, p.product, NULL, NULL, NULL, NULL, q.qty From quantities q
inner join products P ON p.productID = q.productID
WHERE /* q.productID = @productID AND */ q.colorID IS NULL AND q.sizeID IS NULL
EDIT:
SQL Server parse and compile time: CPU time = 32 ms, elapsed time = 65 ms.
(10 row(s) affected) Table 'sizes'. Scan count 1, logical reads 21, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'colors'. Scan count 1, logical reads 21, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'products'. Scan count 0, logical reads 20, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'quantities'. Scan count 1, logical reads 2, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times: CPU time = 0 ms, elapsed time = 12 ms.
(10 row(s) affected) Table 'products'. Scan count 0, logical reads 20, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'quantities'. Scan count 4, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'sizes'. Scan count 0, logical reads 18, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'colors'. Scan count 0, logical reads 12, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.
It seems to me that it's the second query that is best for performance, the bigger query with unions.
Do anyone of you got a clue why this happends or do I have to provide with more information (table info and such things) ?
Execution plan:
Thanks!
SET STATSTICS IO, TIME ON
and check for logical reads, cpu time, etc That will show you which one is better in terms of Disk I/O and CPU.