Luis Cazares (9/17/2012)

---

I came out with the same code as Jeff showed in the article, but I believe that Scott's code would be better as it is a single table scan instead of 2 or 3.

And now call me crazy, but execution plans show one thing and time indicators show another on performance issues. Maybe is because I'm using temp tables instead of normal physical tables, but the statement with 3 table scans is performing faster than the other two. And the statement with 1 table scan is the slowest.

Can anyone confirm or deny this?

This is the code I used:

--insert into #product_details

--SELECT TOP 1000000

--'CUST' + CAST( (ROW_NUMBER() OVER( ORDER BY (SELECT NULL)) / 4) + 6 AS varchar(10)),

--ABS(CHECKSUM(NEWID())) % 5,

--GETDATE()

--FROM sys.all_columns ac1

-- CROSS JOIN sys.all_columns ac2

DECLARE @DATE datetime2, @dummy varchar(50)

SET @DATE = SYSDATETIME()

SELECT @dummy = CustId

FROM(

SELECT CustId

FROM #product_details

WHERE ProdId IN( 1,4)

GROUP BY CustId HAVING COUNT(DISTINCT prodid) = 2

EXCEPT

SELECT CustId

FROM #product_details

WHERE ProdId = 0) t

PRINT DATEDIFF( ns, @DATE, SYSDATETIME())

SET @DATE = SYSDATETIME()

SELECT @dummy = CustId

FROM

(SELECT CustId

FROM #product_details

WHERE ProdId = 1

AND CustId IN (SELECT DISTINCT CustId FROM #product_details WHERE ProdId = 4)

EXCEPT

SELECT CustId

FROM #product_details

WHERE ProdId = 0) AS A

PRINT DATEDIFF( ns, @DATE, SYSDATETIME())

SET @DATE = SYSDATETIME()

SELECT

@dummy = CustId

FROM #product_details

GROUP BY

CustId

HAVING

MAX(CASE WHEN ProdId = 0 THEN 1 ELSE 0 END) = 0 AND

MAX(CASE WHEN ProdId = 1 THEN 1 ELSE 0 END) = 1 AND

MAX(CASE WHEN ProdId = 4 THEN 1 ELSE 0 END) = 1

ORDER BY

CustId

PRINT DATEDIFF( ns, @DATE, SYSDATETIME())

SET @DATE = SYSDATETIME()