SQL Padawan (8/20/2012)
The code below ran pretty fast on my desktop. Give it a try:
SELECT DISTINCT @holder1 = s1.SaleID,@holder2 = LocationIDFROM #Sale s1
INNER JOIN #SaleLocationXref SLX1 ON S1.SaleID = SLX1.SaleID
INNER JOIN #SaleItemXref SIX ON SIX.SaleID = s1.SaleID
WHERE EXISTS(
SELECT 1
FROM #Sale s2
INNER JOIN #SaleLocationXref SLX2 ON s2.SaleID = SLX2.SaleID
INNER JOIN #SaleItemXref SIX2 ON SIX2.SaleID = s2.SaleID
WHERE s1.SaleID <> s2.SaleID
AND s1.SaleStart <= s2.SaleEnd
AND s2.SaleStart <= s1.SaleEnd
AND SLX2.LocationID = SLX1.LocationID
AND SIX.ItemID = SIX2.ItemID)
GROUP BY s1.SaleID,LocationID
+1 SQL Padawan. The force was with you!
---- Original query
SQL Server Execution Times:
CPU time = 3433 ms, elapsed time = 942 ms.
---- Dwain's query
SQL Server Execution Times:
CPU time = 3230 ms, elapsed time = 1789 ms.
---- SQL Padawan
SQL Server Execution Times:
CPU time = 124 ms, elapsed time = 123 ms.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St