SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Find Customers Who Bought "A" and "B" But Not "C" (SQL Spackle)


Find Customers Who Bought "A" and "B" But Not "C" (SQL Spackle)

Author
Message
ColdCoffee
ColdCoffee
SSCrazy Eights
SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)

Group: General Forum Members
Points: 8703 Visits: 5555
chintan.j.gandhi (3/29/2012)
How about this? Although i agree query might get longer when there would be more than 3 items


SELECT DISTINCT CustomerID
FROM #Purchase P
WHERE EXISTS (SELECT 1 FROM #Purchase P1
WHERE P.customerid = P1.customerid
AND P1.productcode = 'A'
)
AND EXISTS (SELECT 1 FROM #Purchase P2
WHERE P.customerid = P2.customerid
AND P2.productcode = 'B'
)
AND NOT EXISTS (SELECT 1 FROM #Purchase P3
WHERE P.customerid = P3.customerid
AND P3.productcode = 'C'
)



YOu can combine A and B code chunks and leave the NOT EXISTS as it, reducing it tp 2x slower than the others.
Michael Lysons
Michael Lysons
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2218 Visits: 1452
Great article and discussion. Learned some new stuff, got some "food for thought" - nice one, peeps.
Dalibor Margotic
Dalibor Margotic
SSC-Addicted
SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)

Group: General Forum Members
Points: 449 Visits: 388
You need to eliminate hash join and Index scan.

With this query you can do that using INTERSECT AND EXCEPT

   SELECT CustomerID
FROM #Purchase
WHERE ProductCode = 'A'
INTERSECT
SELECT CustomerID
FROM #Purchase
WHERE ProductCode = 'B'
EXCEPT
SELECT CustomerID
FROM #Purchase
WHERE ProductCode = 'C'



Or, this one using CTE AND JOINS:

;WITH CTE AS
(
SELECT CustomerID
FROM #Purchase
WHERE ProductCode = 'A'
GROUP BY CustomerID
)
, CTE2 AS
(
SELECT CustomerID
FROM #Purchase
WHERE ProductCode = 'B'
GROUP BY CustomerID
)
, CTE3 AS
(
SELECT CustomerID
FROM #Purchase
WHERE ProductCode = 'C'
GROUP BY CustomerID

)
select CTE.CustomerID from CTE
left join CTE3 ON
CTE.CustomerID = CTE3.CustomerID
where CTE.CustomerID in (SELECT CustomerID FROM CTE2)
AND CTE3.CustomerID IS NULL
GROUP BY CTE.CustomerID


Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: General Forum Members
Points: 61989 Visits: 13297
Dalibor Margotic (3/29/2012)
You need to eliminate hash join and Index scan.

With this query you can do that using INTERSECT AND EXCEPT
...

Or, this one using CTE AND JOINS:

...


In the CTE and JOINS query, I would replace the IN clause with an EXISTS clause.
But that could be personal preference.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
ALZDBA
ALZDBA
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29563 Visits: 8986
Another great SQL Spackle, Jeff w00t

Koen Verbeeck (3/29/2012)
Dalibor Margotic (3/29/2012)
You need to eliminate hash join and Index scan.

With this query you can do that using INTERSECT AND EXCEPT
...

Or, this one using CTE AND JOINS:

...


In the CTE and JOINS query, I would replace the IN clause with an EXISTS clause.
But that could be personal preference.


One should certainly replace the IN by EXISTS or a left join adding a where clause containing the "outer-joined object key " is null

or at least compare their performance ;-)

interesting series to read on this matter:
- http://sqlinthewild.co.za/index.php/2010/03/23/left-outer-join-vs-not-exists/

Johan


Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere w00t

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
mister.magoo
mister.magoo
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10531 Visits: 7891
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

MM


select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);




  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Wavesailor
    Wavesailor
    Grasshopper
    Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

    Group: General Forum Members
    Points: 21 Visits: 13
    Thanks for article and comments - I learnt a lot.

    In MySQL :-D this is the fastest by far:


    SELECT
    CustomerId
    FROM Purchase
    WHERE ProductCode IN ('A','B', 'C')
    GROUP BY CustomerID
    having sum(case when ProductCode = 'A' then 1 else 0 end) > 0
    and sum(case when ProductCode = 'B' then 1 else 0 end) > 0
    and sum(case when ProductCode = 'C' then 1 else 0 end) = 0



    Unfortunately MySQL does not have the EXCEPT operator.

    -jj
    Jeff Moden
    Jeff Moden
    SSC Guru
    SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)

    Group: General Forum Members
    Points: 213431 Visits: 41977
    Hi folks,

    Thanks for the great discussion going on and the thoughtful feedback. I'd love to jump in this instant but I'm on my way to work. I'll be back tonight to try to answer some of the questions.

    Thanks, again.

    --Jeff Moden

    RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

    Helpful Links:
    How to post code problems
    How to post performance problems
    Forum FAQs
    venoym
    venoym
    SSCommitted
    SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

    Group: General Forum Members
    Points: 1777 Visits: 2082
    Here's another alternative. I haven't tested the performance as I don't have a setup to do that, and I don't know if it will be similar to Jeff's in query plan. It does avoid the COUNT(DISTINCT...) that I read a while back (can't find the linky) was sometimes bad (something about rewinds).

    EDIT: Eh, nevermind... you can ignore this one... Did a small test and it's between 5 and 10x slower than other methods with about 2x the reads.
    Second Edit: BUT, it's easier to adjust if you know that the situation is a random number of "Purchased these" with a single "but didn't purchase this". Most of the other methods rely on replicating a block of SQL for each item in the list of "Purchased". I think both Jeff's (and mine) work without that restriction.


    SELECT
    b.CustomerID
    FROM
    (
    SELECT
    a.CustomerID
    FROM
    (
    SELECT
    DISTINCT
    CustomerID,
    Product
    FROM
    Purchase
    WHERE
    Product IN ('A','B')) AS a
    GROUP BY
    a.CustomerID
    HAVING
    COUNT(Product) = 2
    ) AS b LEFT OUTER JOIN
    Purchase p ON b.CustomerID = p.CustomerID AND p.Product = 'C'
    WHERE
    p.Product IS NULL



    sdorris 90134
    sdorris 90134
    Mr or Mrs. 500
    Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)

    Group: General Forum Members
    Points: 595 Visits: 625
    As always, Jeff turns complex problems into simple solutions. I'm still looking for "SQL Spackles" by Jeff Moden at Barnes & Noble.
    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