Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


select query


select query

Author
Message
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8504 Visits: 18107
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.
General Disclaimer:
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?


How to post data/code on a forum to get the best help: Option 1 / Option 2
ScottPletcher
ScottPletcher
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3939 Visits: 6671
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)

Prosecutor James Blackburn, in closing argument in the "Fatal Vision" murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search