Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

select query Expand / Collapse
Author
Message
Posted Monday, September 17, 2012 2:04 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 8:01 AM
Points: 4,047, Visits: 9,206
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()




Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1360440
Posted Monday, September 17, 2012 2:40 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:39 AM
Points: 2,332, Visits: 3,511
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()




Timing seems that way.

I would still be leery of additional I/O vs other time, but if you are willing to scan the table 3 times to get response time, then I would say do this:


SELECT 
CustId
FROM #product_details pd1
WHERE
prodid = 1 AND
EXISTS(SELECT 1 FROM #product_details pd2 WHERE pd2.custid = pd1.custid AND pd2.prodid = 4) AND
NOT EXISTS(SELECT 1 FROM #product_details pd3 WHERE pd3.custid = pd1.custid AND pd3.prodid = 0)



SQL DBA,SQL Server MVP('07, '08, '09)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1360450
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse