InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 18 May 2013 19:12:40 GMT20http://www.sqlservercentral.com/Forums/Topic1274832-203-1.aspxAnother way to skin the cat? (results are for the million-row table provided in the article)(apologies if someone already posted this method)[code="sql"]--SET STATISTICS IO ON--SET STATISTICS TIME ONSELECT CustomerIDFROM #PurchaseWHERE ProductCode IN('A','B','C') -- only A,B,C, AB,AC,BC and ABCGROUP BY CustomerIDHAVING COUNT(DISTINCT ProductCode) = 2 -- only AB,AC and BCAND MAX(ProductCode) = 'B' -- only AB! :)/*(6763 row(s) affected)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.Table '#Purchase'. Scan count 3, logical reads 226, 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 = 187 ms, elapsed time = 187 ms.*/[/code]Tue, 16 Apr 2013 03:48:22 GMTThe Wizard Of Ozhttp://www.sqlservercentral.com/Forums/Topic1274832-203-1.aspxGreat article Jeff.Awesome discussion by all! Thanks!Sun, 01 Jul 2012 12:50:29 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic1274832-203-1.aspxBelow is the sql to show how much cpu time and logical IO's it takes to find the customers who bought A,B but not C.Also, I have done a performance comparison when you want to find the details from Purchase tablefor the customer who bought A, B but not C.The method using parttion by which eliminates join with purchase column is way too expensive...Thus before using any new method benchmark it against existing best solution... Just to make sure which method is better..Follow one rule..Benchmark benchmark Benchmark.....[code="sql"]use tempdbgo/* --uncomment this to create purchase table.. This script to generate the 1 million rows is --created by Jeff Moden on sql server central . com--===== This code takes about 23 seconds to run on a 10 year old, -- single CPU desktop machine.--===== Conditionally drop the test table to make reruns in SSMS easier. -- This is NOT a part of the solution. IF OBJECT_ID('tempdb..Purchase','U') IS NOT NULL DROP TABLE Purchase;--===== Create and populate the test table. -- This is NOT a part of the solution. SELECT TOP (1000000) PurchaseID = IDENTITY(INT,1,1), CustomerID = ABS(CHECKSUM(NEWID())) % 50000 + 1, ProductCode = CHAR(ABS(CHECKSUM(NEWID())) % 26 + 65) INTO Purchase FROM sys.all_columns ac1 CROSS JOIN sys.all_columns ac2;--===== Add the expected PK ALTER TABLE Purchase ADD PRIMARY KEY CLUSTERED (PurchaseID);--===== Can you guess which index will be used? CREATE INDEX IX_Purchase_CustomerID_ProductCode ON Purchase (CustomerID, ProductCode); CREATE INDEX IX_Purchase_ProductCode_CustomerID ON Purchase (ProductCode, CustomerID) update statistics Purchase with fullscan*/ --- find only the customer ids who bought a,b but not cwith cte1as(select CustomerId,MAX(case when ProductCode = 'A' then 1 else 0 end) +max(case when ProductCode = 'B' then 2 else 0 end) + max(case when ProductCode = 'C' then 4 else 0 end ) as statusfrom Purchasegroup by CustomerID)select * from cte1 where status = 3--Above uses a non clustered index scan and clocked 1867 logical IO and around 800 ms cpu and elapsed time.--query to get the details of customers along with product code and using join back to the original table..with cte1as(select CustomerId,MAX(case when ProductCode = 'A' then 1 else 0 end) +max(case when ProductCode = 'B' then 2 else 0 end) + max(case when ProductCode = 'C' then 4 else 0 end ) as statusfrom Purchasegroup by CustomerID)select p.CustomerId,p.ProductCode,cte1.status from cte1 inner join purchase p on cte1.CustomerId = p.CustomerIdwhere status = 3and p.ProductCode not in ('C'); --as we --It clocked 3734 Io's and 1200 ms CPU time and 1500 ms of elapsed time for 137 K rows..This is quite fast--This is using without join but using parttion by ..This is performaing badly.. --Thus do not try the new methods just to make the statement simple..--Always benchmark with the best method you have with any new method which is introduced in the new versions..--It is clocking 2.2 million IO's approx...Which is way too high 7533 ms CPU time... and 2853 ms elapsed time for 137 k rowswith cte1as(select CustomerID,ProductCode, case when ProductCode = 'A' then 1 else 0 end as IsAExist, case when ProductCode = 'B' then 2 else 0 end as IsBExist, case when ProductCode = 'C' then 4 else 0 end as IsCExistfrom Purchase),cte2as(select CustomerID,ProductCode, MAX(IsAExist) over(PARTITION by CustomerID) + MAX(IsBExist)over(PARTITION by CustomerID) + MAX(IsCExist)over(PARTITION by CustomerID) as status from cte1)select * from cte2where status = 3 --option(maxdop 1); --a and b but not Cgo--/*To compare the above...without using parttitionLogical IO CPU Time Elapsed Time3734 800ms 800ms2201871 7500ms 2850ms*/[/code]Fri, 11 May 2012 10:23:46 GMTGullimeelhttp://www.sqlservercentral.com/Forums/Topic1274832-203-1.aspxHi JeffIt is just sum of 2 power (0 for A, 1 for B ,2 for C,3 for D ..and so on)..Thus if you have all 3 codes then 2* 0 + 2* 1 + 2*2 = 1 + 2 + 4. = 7if any of the code is missing the value for that will be 0. That way you know which code is there and which one are missing... Consider these as bits and bits are set to 1for the codes which are there for a given customer id and for others these are set to 0.Hopefully, this will help..Fri, 11 May 2012 09:42:28 GMTGullimeelhttp://www.sqlservercentral.com/Forums/Topic1274832-203-1.aspx[quote][b]Gullimeel (5/11/2012)[/b][hr]select * from (select id,max(case when cd = 'A' then 1 else 0 end) + max(case when cd = 'B' then 2 else 0 end) + max(case when cd = 'C' then 4 else 0 end) status from a--where cd in ('A','B','C')group by id) dtawhere 1=1--and status = 3 -- a,b but not c--and status = 5--does have a,c but not b--and status = 6--does have b,c but not a--and status = 7 --all 3--and status = 0 --none of them --and status = 1 -- just A--and status = 2 -- just band status = 4 -- just CChange the id with CustomerId ,cd with ProductCode. You are done.You can use anaytical function using parttion instead of group if you need all the columns from the table. You can use the same concept and could get the results for any combination of product codes.[/quote]Ummm... that's real nice but what builds the status column? Some magical analytical function that you haven't shown us?Fri, 11 May 2012 08:37:31 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1274832-203-1.aspxselect * from (select id,max(case when cd = 'A' then 1 else 0 end) + max(case when cd = 'B' then 2 else 0 end) + max(case when cd = 'C' then 4 else 0 end) status from a--where cd in ('A','B','C')group by id) dtawhere 1=1--and status = 3 -- a,b but not c--and status = 5--does have a,c but not b--and status = 6--does have b,c but not a--and status = 7 --all 3--and status = 0 --none of them --and status = 1 -- just A--and status = 2 -- just band status = 4 -- just CChange the id with CustomerId ,cd with ProductCode. You are done.You can use anaytical function using parttion instead of group if you need all the columns from the table. You can use the same concept and could get the results for any combination of product codes.Fri, 11 May 2012 03:24:39 GMTGullimeelhttp://www.sqlservercentral.com/Forums/Topic1274832-203-1.aspxselect * from (select id,max(case when cd = 'A' then 1 else 0 end) + max(case when cd = 'B' then 2 else 0 end) + max(case when cd = 'C' then 4 else 0 end) status from a--where cd in ('A','B','C')group by id) dtawhere 1=1--and status = 3 -- a,b but not c--and status = 5--does have a,c but not b--and status = 6--does have b,c but not a--and status = 7 --all 3--and status = 0 --none of them --and status = 1 -- just A--and status = 2 -- just band status = 4 -- just CReplace Id with CustomerId and cd with ProductCode. You can generalize the code the way you want..You can use analytic function instead of group by as well.Fri, 11 May 2012 03:21:24 GMTGullimeelhttp://www.sqlservercentral.com/Forums/Topic1274832-203-1.aspx[quote][b]michal.lisinski (4/2/2012)[/b][hr][quote][b]Jonathan AC Roberts (4/2/2012)[/b][hr][quote][b]michal.lisinski (4/2/2012)[/b][hr]Imho[code="other"]SELECT DISTINCT CustomerIDFROM [#Purchase]WHERE (ProductCode IN ('A','B')) AND (CustomerID NOT IN (SELECT DISTINCT CustomerID FROM [#Purchase] WHERE (ProductCode = 'C')))GROUP BY CustomerID, ProductCodeHAVING (COUNT(CustomerID) = 1)[/code]RegardsMike[/quote]This also will select customers who have purchased just one of 'A' or 'B' and not purchased 'C'. the requirement was that they should have purchased both 'A' and 'B' and not purchased 'C'.[/quote] Hmm, r u sure? Customer no 6 took product A and no B, customer 7 took product B and no A, both didn't take C, but query doesn't return them.RegardsMike [/quote]Hi Mike, I tried the following on your code:[code="sql"];WITH [#Purchase] AS( SELECT 'A' ProductCode, 1 CustomerId UNION ALL SELECT 'B' ProductCode, 1 CustomerId UNION ALL SELECT 'A' ProductCode, 2 CustomerId UNION ALL SELECT 'B' ProductCode, 3 CustomerId UNION ALL SELECT 'A' ProductCode, 4 CustomerId UNION ALL SELECT 'A' ProductCode, 4 CustomerId UNION ALL SELECT 'A' ProductCode, 5 CustomerId UNION ALL SELECT 'B' ProductCode, 5 CustomerId UNION ALL SELECT 'C' ProductCode, 5 CustomerId )SELECT DISTINCT CustomerIDFROM [#Purchase]WHERE (ProductCode IN ('A','B')) AND (CustomerID NOT IN (SELECT DISTINCT CustomerID FROM [#Purchase] WHERE (ProductCode = 'C')))GROUP BY CustomerID, ProductCodeHAVING (COUNT(CustomerID) = 1)[/code]Customer 1 has bought 'A' and 'B' and not 'C' and he gets found correctlyCustomer 2 has bought 'A' and not 'B' and not 'C' and he gets found incorrectlyCustomer 3 has bought not 'A' and 'B' and not 'C' and he gets found incorrectlyCustomer 4 has bought two 'A' and not 'B' and not 'C' and he doesn't get found correctlyCustomer 5 has bought 'A' and 'B' and 'C' and he doesn't get found correctlyMon, 02 Apr 2012 07:32:10 GMTJonathan AC Robertshttp://www.sqlservercentral.com/Forums/Topic1274832-203-1.aspx[quote][b]Jonathan AC Roberts (4/2/2012)[/b][hr][quote][b]michal.lisinski (4/2/2012)[/b][hr]Imho[code="other"]SELECT DISTINCT CustomerIDFROM [#Purchase]WHERE (ProductCode IN ('A','B')) AND (CustomerID NOT IN (SELECT DISTINCT CustomerID FROM [#Purchase] WHERE (ProductCode = 'C')))GROUP BY CustomerID, ProductCodeHAVING (COUNT(CustomerID) = 1)[/code]RegardsMike[/quote]This also will select customers who have purchased just one of 'A' or 'B' and not purchased 'C'. the requirement was that they should have purchased both 'A' and 'B' and not purchased 'C'.[/quote] Hmm, r u sure? Customer no 6 took product A and no B, customer 7 took product B and no A, both didn't take C, but query doesn't return them.RegardsMike Mon, 02 Apr 2012 07:15:39 GMTmichal.lisinskihttp://www.sqlservercentral.com/Forums/Topic1274832-203-1.aspx[quote][b]Jonathan AC Roberts (4/2/2012)[/b][hr][quote][b]michal.lisinski (4/2/2012)[/b][hr]Imho[code="other"]SELECT DISTINCT CustomerIDFROM [#Purchase]WHERE (ProductCode IN ('A','B')) AND (CustomerID NOT IN (SELECT DISTINCT CustomerID FROM [#Purchase] WHERE (ProductCode = 'C')))GROUP BY CustomerID, ProductCodeHAVING (COUNT(CustomerID) = 1)[/code]RegardsMike[/quote]This also will select customers who have purchased just one of 'A' or 'B' and not purchased 'C'. the requirement was that they should have purchased both 'A' and 'B' and not purchased 'C'.[/quote]Mon, 02 Apr 2012 07:14:17 GMTmichal.lisinskihttp://www.sqlservercentral.com/Forums/Topic1274832-203-1.aspxHmm, r u sure? Customer no 6 took product A and no B, customer 7 took product B and no A, both didn't take C, but query doesn't return them.RegardsMikeMon, 02 Apr 2012 07:00:49 GMTmichal.lisinskihttp://www.sqlservercentral.com/Forums/Topic1274832-203-1.aspx[quote][b]Jeff Moden (3/31/2012)[/b][hr][quote][b]Dean Cochrane (3/30/2012)[/b][hr][quote][b]Berl (3/30/2012)[/b][hr]Although CPU time difference is not very great, the difference is much bigger for the elapsed time.[b] [/quote]Elapsed time includes wait time and is not a good measure of performance.[/quote]Actually, it is. The wait time you speak of also includes I/O time and that can be a major part of a performance problem. I do agree, though, that you really need to take "time to display" out of the picture because it will severly mask problems. It takes a certain amount of time to display a million rows and can make differences between two methods seem much more trivial than they might be.[/quote]Well depends, the elapsed time can an ok performance indicator, however when you run the queries for testing back to back in the same batch without clearing the buffers you may get a false sense of performance from elapsed time and many other stats. I have a local test SQL Server which I always test by stopping the instance then starting so I can be sure everything is clear for testing. But the query plans, so I would be carefull in saying one scenario over another is truely best if you compare in a single batch. Now all that said, a huge difference in elapsed time shows a performance change and when I say huge took 30minutes to run first time then 30 seconds with second query.Mon, 02 Apr 2012 06:35:27 GMTAntares686http://www.sqlservercentral.com/Forums/Topic1274832-203-1.aspx[quote][b]michal.lisinski (4/2/2012)[/b][hr]Imho[code="other"]SELECT DISTINCT CustomerIDFROM [#Purchase]WHERE (ProductCode IN ('A','B')) AND (CustomerID NOT IN (SELECT DISTINCT CustomerID FROM [#Purchase] WHERE (ProductCode = 'C')))GROUP BY CustomerID, ProductCodeHAVING (COUNT(CustomerID) = 1)[/code]RegardsMike[/quote]This also will select customers who have purchased just one of 'A' or 'B' and not purchased 'C'. the requirement was that they should have purchased both 'A' and 'B' and not purchased 'C'.Mon, 02 Apr 2012 06:31:39 GMTJonathan AC Robertshttp://www.sqlservercentral.com/Forums/Topic1274832-203-1.aspxImho[code="other"]SELECT DISTINCT CustomerIDFROM [#Purchase]WHERE (ProductCode IN ('A','B')) AND (CustomerID NOT IN (SELECT DISTINCT CustomerID FROM [#Purchase] WHERE (ProductCode = 'C')))GROUP BY CustomerID, ProductCodeHAVING (COUNT(CustomerID) = 1)[/code]RegardsMikeMon, 02 Apr 2012 05:38:12 GMTmichal.lisinskihttp://www.sqlservercentral.com/Forums/Topic1274832-203-1.aspxThank you Jeff.Sat, 31 Mar 2012 22:43:38 GMTBerlhttp://www.sqlservercentral.com/Forums/Topic1274832-203-1.aspx[quote][b]Dean Cochrane (3/30/2012)[/b][hr][quote][b]Berl (3/30/2012)[/b][hr]Although CPU time difference is not very great, the difference is much bigger for the elapsed time.[b] [/quote]Elapsed time includes wait time and is not a good measure of performance.[/quote]Actually, it is. The wait time you speak of also includes I/O time and that can be a major part of a performance problem. I do agree, though, that you really need to take "time to display" out of the picture because it will severly mask problems. It takes a certain amount of time to display a million rows and can make differences between two methods seem much more trivial than they might be.Sat, 31 Mar 2012 13:37:45 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1274832-203-1.aspx[quote][b]Berl (3/30/2012)[/b][hr]Although CPU time difference is not very great, the difference is much bigger for the elapsed time.[b] [/quote]Elapsed time includes wait time and is not a good measure of performance.Fri, 30 Mar 2012 16:21:13 GMTDean Cochranehttp://www.sqlservercentral.com/Forums/Topic1274832-203-1.aspxMight I suggest another, old school, alternative that seems to run a little bit faster, which uses a sub-query to eliminate those customers who all ready purchased item "C".Although CPU time difference is not very great, the difference is much bigger for the elapsed time.[b] SELECT CustomerID FROM #Purchase WHERE ProductCode IN ('A','B') [i][u] AND CustomerID NOT IN (SELECT DISTINCT CustomerID FROM #Purchase AS P WHERE ProductCode IN ('C'))[/u][/i] GROUP BY CustomerID HAVING COUNT(DISTINCT ProductCode) = 2[/b] Here are the statistics:[i]Find Customers that did not buy "C" from article(6673 row(s) affected) SQL Server Execution Times: CPU time = 94 ms, elapsed time = 265 ms. Find Customers that did not buy "C" using subquery(6673 row(s) affected) SQL Server Execution Times: CPU time = 93 ms, elapsed time = 202 ms. [/i]Fri, 30 Mar 2012 14:12:03 GMTBerlhttp://www.sqlservercentral.com/Forums/Topic1274832-203-1.aspxselect customer, sum(case where product = 'A' then 1 else 0 end) as A, sum(case where product = 'B' then 1 else 0 end) as B, sum(case where product = 'C' then 1 else 0 end) as C from [table]group by customer having sum(case where product = 'A' then 1 else 0 end) > 1 and sum(case where product = 'B' then 1 else 0 end) > 1and sum(case where product = 'C' then 1 else 0 end) = 0Fri, 30 Mar 2012 08:41:16 GMTp oelkershttp://www.sqlservercentral.com/Forums/Topic1274832-203-1.aspx@JeffThanks for testing.[hr]@spam 17205I think a simpler version might be[code="sql"] SELECT CustomerID , SUM(CASE ProductCode WHEN 'A' THEN 1 ELSE 0 END) AS cnt_A , SUM(CASE ProductCode WHEN 'B' THEN 1 ELSE 0 END) AS cnt_B , SUM(CASE ProductCode WHEN 'C' THEN 1 ELSE 0 END) AS cnt_C FROM #Purchase WHERE ProductCode IN ('A', 'B', 'C') GROUP BY CustomerID Having cnt_A > 0 AND cnt_B > 0 AND cnt_C = 0[/code]Or to let the inner/initial query just do the string-to-number translation.[code="sql"]SELECT CustomerID , SUM(Case_A) AS cnt_A , SUM(Case_B) AS cnt_B , SUM(Case_C) AS cnt_CFROM ( SELECT CustomerID , CASE ProductCode WHEN 'A' THEN 1 ELSE 0 END AS Case_A , CASE ProductCode WHEN 'B' THEN 1 ELSE 0 END AS Case_B , CASE ProductCode WHEN 'C' THEN 1 ELSE 0 END AS Case_C FROM #Purchase WHERE ProductCode IN ('A', 'B', 'C')) tWHERE cnt_A <> 0 AND cnt_B <> 0 AND cnt_C = 0GROUP BY CustomerID[/code]Fri, 30 Mar 2012 07:08:25 GMTmark hutchinsonhttp://www.sqlservercentral.com/Forums/Topic1274832-203-1.aspxThis one gives you even more flexibility to filter on/output counts of each product:[code="sql"]SELECT CustomerID, cnt_A, cnt_B, cnt_CFROM ( SELECT CustomerID , SUM(CASE ProductCode WHEN 'A' THEN 1 ELSE 0 END) AS cnt_A , SUM(CASE ProductCode WHEN 'B' THEN 1 ELSE 0 END) AS cnt_B , SUM(CASE ProductCode WHEN 'C' THEN 1 ELSE 0 END) AS cnt_C FROM #Purchase WHERE ProductCode IN ('A', 'B', 'C') GROUP BY CustomerID) tWHERE cnt_A > 0 AND cnt_B > 0 AND cnt_C = 0[/code]Fri, 30 Mar 2012 04:33:26 GMTspam 17205http://www.sqlservercentral.com/Forums/Topic1274832-203-1.aspxHi guys,I didn't look if this solution has allready been posted but using CTE ,intersect and exceptfor 10 mil rows it looks like this[code="sql"]with eligible_customer_a as(select distinct CustomerID from #Purchase where ProductCode ='A'),eligible_customer_b as (select distinct customerid from #Purchase where ProductCode='B'),non_eligible_customer_c as(select distinct customerid from #Purchase where ProductCode='C')select customerid from eligible_customer_aintersectselect customerid from eligible_customer_bexceptselect customerid from non_eligible_customer_c[/code]And the statistics are SQL Server parse and compile time: CPU time = 5 ms, elapsed time = 5 ms.(30 row(s) affected) SQL Server Execution Times: CPU time = 219 ms, elapsed time = 227 ms.Fri, 30 Mar 2012 02:37:39 GMTIpsehttp://www.sqlservercentral.com/Forums/Topic1274832-203-1.aspx[quote][b]mark hutchinson (3/29/2012)[/b][hr]@ColdCoffeeThat is why I used a [b]Having[/b] clause[/quote]You are right; i did not notice that u are choosing both CustomerID and ProductCode in your sub-query.I thot you were only CustomerID. Apologies.Thu, 29 Mar 2012 22:18:41 GMTColdCoffeehttp://www.sqlservercentral.com/Forums/Topic1274832-203-1.aspx[quote][b]ColdCoffee (3/29/2012)[/b][hr][quote][b]mark hutchinson (3/29/2012)[/b][hr]@JeffI was thinking about something that might look like this:[code="sql"]FROM (Select Distinct CustomerID, ProductCode From #Purchase WHERE ProductCode In ('A', 'B') ) p1[/code][/quote]This will bring all the cusotmerIDs that are only 'A' [b]or [/b]'B' and not 'C'. The problem is about bringing 'A' [b]and [/b]'B' but not 'C'[/quote]Actually, I tested the code against the original data example I posted in the article and it correctly returns 1,2,3. It takes about the same amount of time as the code in the article.[font="Courier New"]SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms.SQL Server Execution Times: CPU time = 266 ms, elapsed time = 316 ms.[/font]Thu, 29 Mar 2012 21:45:41 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1274832-203-1.aspx@ColdCoffeeThat is why I used a [b]Having[/b] clauseThu, 29 Mar 2012 21:41:18 GMTmark hutchinsonhttp://www.sqlservercentral.com/Forums/Topic1274832-203-1.aspx[quote][b]mark hutchinson (3/29/2012)[/b][hr]@JeffI was thinking about something that might look like this:[code="sql"]FROM (Select Distinct CustomerID, ProductCode From #Purchase WHERE ProductCode In ('A', 'B') ) p1[/code][/quote]This will bring all the cusotmerIDs that are only 'A' [b]or [/b]'B' and not 'C'. The problem is about bringing 'A' [b]and [/b]'B' but not 'C'Thu, 29 Mar 2012 20:50:46 GMTColdCoffeehttp://www.sqlservercentral.com/Forums/Topic1274832-203-1.aspx@JeffI was thinking about something that might look like this:[code="sql"]SELECT DISTINCT p1.CustomerID, Count(p1.CustomerID)FROM (Select Distinct CustomerID, ProductCode From #Purchase WHERE ProductCode In ('A', 'B') ) p1WHERE NOT EXISTS (SELECT * FROM #Purchase p3 WHERE p3.CustomerID = p1.CustomerID AND p3.ProductCode = 'C'))Group By p1.CustomerIDHaving Count(p1.CustomerID) =2[/code]Thu, 29 Mar 2012 20:42:12 GMTmark hutchinsonhttp://www.sqlservercentral.com/Forums/Topic1274832-203-1.aspx[quote][b]mister.magoo (3/29/2012)[/b][hr]Yep, I think that's flogged that one to death. I'm sleepy. G'night.[/quote]Heh... yeah. And if you've ever seen a picture of me, you know I can definitely use a "beauty sleep". :-P Good night folks and thank you all again for making this a really fun and informative discussion!Thu, 29 Mar 2012 20:11:21 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1274832-203-1.aspxYep, I think that's flogged that one to death. I'm sleepy. G'night.Thu, 29 Mar 2012 20:04:27 GMTmister.magoohttp://www.sqlservercentral.com/Forums/Topic1274832-203-1.aspx[quote][b]mister.magoo (3/29/2012)[/b][hr][strike]But I think this just highlights how variable results can be depending on the actual distribution of the data....[/quote]And number of processors. Here's the 10M row 500K customer run for the Magoo 2 code on a single processor.[font="Courier New"]========== Code From Article ===========================================SQL Server Execution Times: CPU time = 2609 ms, elapsed time = 2750 ms.========== Arjun S =====================================================SQL Server Execution Times: CPU time = 844 ms, elapsed time = 872 ms.========== tommyh =====================================================SQL Server Execution Times: CPU time = 3219 ms, elapsed time = 3330 ms.========== Toby Harman =====================================================SQL Server Execution Times: CPU time = 3265 ms, elapsed time = 3345 ms.========== James Dingle =====================================================SQL Server Execution Times: CPU time = 2828 ms, elapsed time = 2824 ms.========== chintan.j.gandhi =====================================================SQL Server Execution Times: CPU time = 7094 ms, elapsed time = 7148 ms.========== Dalibor Margotic CTE (First method already posted by Arjun S) =====SQL Server Execution Times: CPU time = 1047 ms, elapsed time = 1062 ms.========== MAGOO =====================================================SQL Server Execution Times: CPU time = 906 ms, elapsed time = 906 ms.========== Venoym =====================================================SQL Server Execution Times: CPU time = 3703 ms, elapsed time = 3768 ms.========== Jonathan AC Roberts =====================================================SQL Server Execution Times: CPU time = 860 ms, elapsed time = 885 ms.========== bob.probst =====================================================SQL Server Execution Times: CPU time = 2484 ms, elapsed time = 2580 ms.========== Vyengr =====================================================SQL Server Execution Times: CPU time = 938 ms, elapsed time = 969 ms.========== ejoell 66477 =====================================================SQL Server Execution Times: CPU time = 2640 ms, elapsed time = 2673 ms.========== David Rueter =====================================================SQL Server Execution Times: CPU time = 2516 ms, elapsed time = 2584 ms.========== ColdCoffee =====================================================SQL Server Execution Times: CPU time = 3609 ms, elapsed time = 3645 ms.Warning: Null value is eliminated by an aggregate or other SET operation.========== MAGOO 2 =====================================================SQL Server Execution Times: CPU time = 2016 ms, elapsed time = 2041 ms.[/font]Thu, 29 Mar 2012 20:02:35 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1274832-203-1.aspxIt's amazing what adding just a couple of hundred thousand customers will do. :-) Here's the 10 million row run with 500,000 customers like Magoo had instead of just the original 50,000.Again, it's amazing that all the code submitted is within a second or two of each other across 10 million rows of data. Anyone remember where a 50,000 row database was considered to be "huge"? I sure do.[font="Courier New"]========== Code From Article ===========================================SQL Server Execution Times: CPU time = 2656 ms, elapsed time = 2895 ms.[b]========== Arjun S =====================================================SQL Server Execution Times: CPU time = 891 ms, elapsed time = 893 ms.[/b]========== tommyh =====================================================SQL Server Execution Times: CPU time = 3281 ms, elapsed time = 3273 ms.========== Toby Harman =====================================================SQL Server Execution Times: CPU time = 3281 ms, elapsed time = 3307 ms.========== James Dingle =====================================================SQL Server Execution Times: CPU time = 2734 ms, elapsed time = 2751 ms.========== chintan.j.gandhi =====================================================SQL Server Execution Times: CPU time = 7140 ms, elapsed time = 7274 ms.========== Dalibor Margotic CTE (First method already posted by Arjun S) =====SQL Server Execution Times: CPU time = 1079 ms, elapsed time = 1088 ms.========== MAGOO =====================================================SQL Server Execution Times: CPU time = 954 ms, elapsed time = 953 ms.========== Venoym =====================================================SQL Server Execution Times: CPU time = 3985 ms, elapsed time = 4758 ms.[b]========== Jonathan AC Roberts =====================================================SQL Server Execution Times: CPU time = 859 ms, elapsed time = 864 ms.[/b]========== bob.probst =====================================================SQL Server Execution Times: CPU time = 2562 ms, elapsed time = 2937 ms.========== Vyengr =====================================================SQL Server Execution Times: CPU time = 953 ms, elapsed time = 948 ms.========== ejoell 66477 =====================================================SQL Server Execution Times: CPU time = 2719 ms, elapsed time = 2723 ms.========== David Rueter =====================================================SQL Server Execution Times: CPU time = 2563 ms, elapsed time = 2623 ms.========== ColdCoffee =====================================================SQL Server Execution Times: CPU time = 3687 ms, elapsed time = 3814 ms.Warning: Null value is eliminated by an aggregate or other SET operation.[/font]Thu, 29 Mar 2012 19:57:09 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1274832-203-1.aspx[strike]And the original 1M rows on my QUAD/4GB[/strike]Another 10M rows with MOD 50000 and a new codeSQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms.========== Code From Article =========================================== SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 624 ms, elapsed time = 647 ms.========== Arjun S ===================================================== SQL Server Execution Times: CPU time = 281 ms, elapsed time = 293 ms.========== tommyh ===================================================== SQL Server Execution Times: CPU time = 1388 ms, elapsed time = 681 ms.========== Toby Harman ===================================================== SQL Server Execution Times: CPU time = 1435 ms, elapsed time = 680 ms.========== James Dingle ===================================================== SQL Server Execution Times: CPU time = 1872 ms, elapsed time = 1993 ms.========== chintan.j.gandhi ===================================================== SQL Server Execution Times: CPU time = 2839 ms, elapsed time = 3190 ms.========== Dalibor Margotic CTE (First method already posted by Arjun S) ===== SQL Server Execution Times: CPU time = 281 ms, elapsed time = 288 ms.========== MAGOO ===================================================== SQL Server Execution Times: CPU time = 265 ms, elapsed time = 298 ms.========== Venoym ===================================================== SQL Server Execution Times: CPU time = 484 ms, elapsed time = 537 ms.[b]========== Jonathan AC Roberts ===================================================== SQL Server Execution Times: CPU time = 172 ms, elapsed time = 179 ms.[/b]========== bob.probst ===================================================== SQL Server Execution Times: CPU time = 16848 ms, elapsed time = 4631 ms.========== Vyengr ===================================================== SQL Server Execution Times: CPU time = 265 ms, elapsed time = 292 ms.========== ejoell 66477 ===================================================== SQL Server Execution Times: CPU time = 16707 ms, elapsed time = 4708 ms.========== David Rueter ===================================================== SQL Server Execution Times: CPU time = 16662 ms, elapsed time = 5004 ms.========== ColdCoffee ===================================================== SQL Server Execution Times: CPU time = 1342 ms, elapsed time = 668 ms.Warning: Null value is eliminated by an aggregate or other SET operation.[b]========== Magoo2 ===================================================== SQL Server Execution Times: CPU time = 172 ms, elapsed time = 176 ms.[/b]My new version to match Johnathan AC Roberts is a twist on Arjun S:[code="sql"](SELECT CustomerID FROM #Purchase p1 WHERE ProductCode = 'A'EXCEPTSELECT CustomerID FROM #Purchase p1 WHERE ProductCode = 'C')INTERSECTSELECT CustomerID FROM #Purchase p1 WHERE ProductCode = 'B'[/code]But I think this just highlights how variable results can be depending on the actual distribution of the data....Thu, 29 Mar 2012 19:42:14 GMTmister.magoohttp://www.sqlservercentral.com/Forums/Topic1274832-203-1.aspx[quote][b]mister.magoo (3/29/2012)[/b][hr]Jeff, did you tweak the CustomerID MOD value from 50000 at all for that?[/quote]No, I sure didn't. Let me try that on my box. I'll run it up to 500,000 just like you did. And, thanks for posting your test.Thu, 29 Mar 2012 19:42:05 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1274832-203-1.aspxResults for 10M rows with MOD 500000 on CustomerID on QUAD core desktop with 4GB ram.========== Code From Article =========================================== SQL Server Execution Times: CPU time = 1576 ms, elapsed time = 803 ms.[b]========== Arjun S ===================================================== SQL Server Execution Times: CPU time = 343 ms, elapsed time = 367 ms.[/b]========== tommyh ===================================================== SQL Server Execution Times: CPU time = 1764 ms, elapsed time = 808 ms.========== Toby Harman ===================================================== SQL Server Execution Times: CPU time = 1762 ms, elapsed time = 846 ms.========== James Dingle ===================================================== SQL Server Execution Times: CPU time = 1263 ms, elapsed time = 1349 ms.========== chintan.j.gandhi ===================================================== SQL Server Execution Times: CPU time = 2683 ms, elapsed time = 3619 ms.[b]========== Dalibor Margotic CTE (First method already posted by Arjun S) ===== SQL Server Execution Times: CPU time = 343 ms, elapsed time = 373 ms.[/b]========== MAGOO ===================================================== SQL Server Execution Times: CPU time = 1014 ms, elapsed time = 1152 ms.========== Venoym ===================================================== SQL Server Execution Times: CPU time = 1997 ms, elapsed time = 4494 ms.========== Jonathan AC Roberts ===================================================== SQL Server Execution Times: CPU time = 2231 ms, elapsed time = 2529 ms.========== bob.probst ===================================================== SQL Server Execution Times: CPU time = 998 ms, elapsed time = 735 ms.========== Vyengr ===================================================== SQL Server Execution Times: CPU time = 998 ms, elapsed time = 1114 ms.========== ejoell 66477 ===================================================== SQL Server Execution Times: CPU time = 1154 ms, elapsed time = 612 ms.========== David Rueter ===================================================== SQL Server Execution Times: CPU time = 1046 ms, elapsed time = 642 ms.========== ColdCoffee ===================================================== SQL Server Execution Times: CPU time = 1590 ms, elapsed time = 800 ms.Warning: Null value is eliminated by an aggregate or other SET operation.Thu, 29 Mar 2012 19:30:09 GMTmister.magoohttp://www.sqlservercentral.com/Forums/Topic1274832-203-1.aspxJeff, did you tweak the CustomerID MOD value from 50000 at all for that?Thu, 29 Mar 2012 19:26:56 GMTmister.magoohttp://www.sqlservercentral.com/Forums/Topic1274832-203-1.aspxHere are the results from a 10 million row run. And, SURPRISE! We have a new winner! (Jonathan AC Roberts ). Also, keep in mind that I'm doing this on a single CPU on an old 1.8GHz box. Your mileage WILL vary! :-)[font="Courier New"]========== Code From Article ===========================================SQL Server Execution Times: CPU time = 1281 ms, elapsed time = 1580 ms.========== Arjun S =====================================================SQL Server Execution Times: CPU time = 703 ms, elapsed time = 745 ms.========== tommyh =====================================================SQL Server Execution Times: CPU time = 2594 ms, elapsed time = 2922 ms.========== Toby Harman =====================================================SQL Server Execution Times: CPU time = 3219 ms, elapsed time = 3576 ms.========== James Dingle =====================================================SQL Server Execution Times: CPU time = 3875 ms, elapsed time = 4254 ms.========== chintan.j.gandhi =====================================================SQL Server Execution Times: CPU time = 9109 ms, elapsed time = 10981 ms.========== Dalibor Margotic CTE (First method already posted by Arjun S) =====SQL Server Execution Times: CPU time = 734 ms, elapsed time = 875 ms.========== MAGOO =====================================================SQL Server Execution Times: CPU time = 719 ms, elapsed time = 768 ms.========== Venoym =====================================================SQL Server Execution Times: CPU time = 1265 ms, elapsed time = 1449 ms.========== Jonathan AC Roberts =====================================================SQL Server Execution Times: CPU time = 437 ms, elapsed time = 461 ms.========== bob.probst =====================================================SQL Server Execution Times: CPU time = 12344 ms, elapsed time = 13928 ms.========== Vyengr =====================================================SQL Server Execution Times: CPU time = 766 ms, elapsed time = 942 ms.========== ejoell 66477 =====================================================SQL Server Execution Times: CPU time = 10500 ms, elapsed time = 11831 ms.========== David Rueter =====================================================SQL Server Execution Times: CPU time = 11110 ms, elapsed time = 12419 ms.========== ColdCoffee =====================================================SQL Server Execution Times: CPU time = 2719 ms, elapsed time = 3013 ms.Warning: Null value is eliminated by an aggregate or other SET operation.[/font]Thu, 29 Mar 2012 19:22:37 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1274832-203-1.aspx[quote][b]Jeff Moden (3/29/2012)[/b][hr]Thanks for the feedback, Andre. I just have to get better at beating the general public at my own game. :-PI love this forum and this community. Like I said, I throw an idea out there and a bunch of people rise to the occasion and share their own thoughts and code... and they usually come up with improvements! To top it off, no one got snotty or nasty about it! Ya just gotta love this place.Thanks folks. I really appreciate it all.[/quote]That's probably one of the greatest discussions followed by a great article I've seen in weeks!I love this place because of things like this. Everyone will contribute their own ideas and expand existing ones. I thank you for starting this, as always.Now what I find really interesting is that queries that use EXISTS and NOT EXISTS run really fast when the data is filtered (ProductCode = 'A') prior to being passed to those operators. See MAGOO and Jonathan AC Roberts code. Something to keep in mind for sure.Thu, 29 Mar 2012 19:10:51 GMTcodebyohttp://www.sqlservercentral.com/Forums/Topic1274832-203-1.aspxJeff, is it possible to run the test on 10 Million row table, if you have time?Thu, 29 Mar 2012 19:06:19 GMTColdCoffeehttp://www.sqlservercentral.com/Forums/Topic1274832-203-1.aspx[quote][b]codebyo (3/29/2012)[/b][hr]Hi, Jeff.Indeed. I was at work and couldn't test it properly.I should've noticed that the IN clause may bring results from either A OR B. :-)That's an excellent article by the way.I use UNION ALL when needed but I've never given EXCEPT or INTERSECT the attention they deserve. :-D[quote]========== Arjun S =====================================================SQL Server Execution Times:CPU time = 78 ms, elapsed time = 100 ms.[/quote]:Wow: A nice combination of INTERSECT and EXCEPT gets the price. Congrats![/quote]Thanks for the feedback, Andre. I just have to get better at beating the general public at my own game. :-PI love this forum and this community. Like I said, I throw an idea out there and a bunch of people rise to the occasion and share their own thoughts and code... and they usually come up with improvements! To top it off, no one got snotty or nasty about it! Ya just gotta love this place.Thanks folks. I really appreciate it all.Thu, 29 Mar 2012 18:59:49 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1274832-203-1.aspxHi, Jeff.Indeed. I was at work and couldn't test it properly.I should've noticed that the IN clause may bring results from either A OR B. :-)That's an excellent article by the way.I use UNION ALL when needed but I've never given EXCEPT or INTERSECT the attention they deserve. :-D[quote]========== Arjun S =====================================================SQL Server Execution Times:CPU time = 78 ms, elapsed time = 100 ms.[/quote]:Wow: A nice combination of INTERSECT and EXCEPT gets the price. Congrats!Thu, 29 Mar 2012 18:51:46 GMTcodebyo