Hi Jeff,

Great Spackle, thanks!

In my tests, using your code to build a million row test, I found this method to be twice as fast for the same logical reads.

SELECT DISTINCT

CustomerID

FROM #Purchase p1

WHERE ProductCode = 'A'

AND EXISTS (SELECT

1

FROM #Purchase p2

WHERE p2.CustomerID = p1.CustomerID

AND p2.ProductCode = 'B')

AND NOT EXISTS (SELECT

1

FROM #Purchase p3

WHERE p3.CustomerID = p1.CustomerID

AND p3.ProductCode = 'C')

I tested this against 10,000,000 rows as well as 1,000,000 and found that it also scales better (IMHO)...

For 10,000,000 rows, the EXCEPT query had these stats:

Table 'Worktable'. Scan count 49955, logical reads 151393, 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.

SQL Server Execution Times:

CPU time = 655 ms, elapsed time = 739 ms.

The EXISTS query had these:

Table 'Worktable'. Scan count 3, logical reads 2156, 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 = 406 ms, elapsed time = 478 ms.

I wondered if you considered this method and if so, what it was that steered you away from it?

Thanks