SQL Server - Query with MAX(CASE ..) and GROUP BY runs too long

Question

I added to my query max() and group by to exclude duplicates and now it's running very long, 56 minutes. But before adding max() and group by it was running 7-8 min. It's ok, I'm using aggregate view in join and it takes time.

Without max() and group by I had duplicates of customers who opted-in and not opted-in. Where I wanted just to put value "1" if I see that customer has OptIn as "1".

Here is my code:

WITH
cteURC AS (
SELECT
distinct scvid
,MAX(CASE WHEN who = 'urc' AND OptIn = '1' THEN 1 ELSE 0 END) AS URC_OptIn
FROM
scv.OptIn
where who = 'urc'
),
cteSponsor AS (
SELECT
distinct scvid
,MAX(CASE WHEN who = 'sponsor/3rd party' AND OptIn = '1' THEN 1 ELSE 0 END) AS Partner_OptIn
FROM
scv.OptIn
where who = 'sponsor/3rd party'
GROUP BY scvid
),
cteLeinster AS (
SELECT
distinct scvid
,MAX(CASE WHEN who = 'leinster rugby' AND OptIn = '1' THEN 1 ELSE 0 END) AS Leinster_OptIn
FROM
scv.OptIn
where who = 'leinster rugby'
) 
SELECT distinct(c.[ScvId])
,Title
,FirstName
,LastName
,EmailAddress
,DateOfBirth
,Address1
,City
,Telephone
,Gender
,URC_OptIn
,Partner_OptIn
,Leinster_OptIn
,MAX(CASE WHEN c.ScvId = T.ScvId AND ProductId = '2019' THEN 1 ELSE 0 END) AS FinalTicketPurchaser_2019
,MAX(CASE WHEN c.ScvId = T.ScvId AND ProductId = '2018' THEN 1 ELSE 0 END) AS FinalTicketPurchaser_2018
FROM scv.vwCustomer c   
LEFT JOIN cteURC U ON c.scvid = U.scvid
LEFT JOIN cteSponsor S ON c.ScvId = S.ScvId
LEFT JOIN cteLeinster L on c.ScvId = L.ScvId
LEFT JOIN vwAggTransaction T ON c.ScvId = T.ScvId
group by c.[ScvId]
Title,
FirstName,
LastName,
EmailAddress,
DateOfBirth,
Address1,
City,
Telephone,
Gender,
URC_OptIn,
Partner_OptIn,
Leinster_OptIn,
order by c.scvid

How can I change query to make it run quicker?

Update

I became brave and re-wrote query. I was hesitating as my friend wrote the ctes and I thought that they are necessary.

This version works well and fast. Thank you everyone. I really appreciate your input!

 SELECT (c.[ScvId])
,Title
,FirstName
,LastName
,EmailAddress
,DateOfBirth
,Address1
,City
,Telephone
,Gender
,MAX(CASE WHEN who = 'urc' AND OptIn = '1' THEN 1 ELSE 0 END) AS URC_OptIn
,MAX(CASE WHEN who = 'sponsor/3rd party' AND OptIn = '1' THEN 1 ELSE 0 END) AS Partner_OptIn
,MAX(CASE WHEN who = 'leinster rugby' AND OptIn = '1' THEN 1 ELSE 0 END) AS Leinster_OptIn
,MAX(CASE WHEN who = 'connacht rugby' AND OptIn = '1' THEN 1 ELSE 0 END) AS Connacht_OptIn
,MAX(CASE WHEN c.ScvId = T.ScvId AND ProductId = '2019' THEN 1 ELSE 0 END) AS FinalTicketPurchaser_2019
,MAX(CASE WHEN c.ScvId = T.ScvId AND ProductId = '2018' THEN 1 ELSE 0 END) AS FinalTicketPurchaser_2018
FROM vwCustomer c
LEFT JOIN OptIn o ON c.scvid = o.scvid
LEFT JOIN vwAggTransaction T ON c.ScvId = T.ScvId
group by c.[ScvId]
,Title
,FirstName
,LastName
,EmailAddress
,DateOfBirth
,Address1
,City
,Telephone
,Gender
order by c.scvid

At a very minimum you should [include the actual Execution Plan](https://stackoverflow.com/a/7359705/1260204), you could use [Paste the Plan](https://www.brentozar.com/pastetheplan/) and share the link in your question. Also try to read it yourself, maybe you can figure out the performance issue(s) with your query. Finally include the [schema DDL](https://en.wikipedia.org/wiki/Data_definition_language). — Igor, Mar 10 '22 at 17:40
A `DISTINCT` with a `GROUP BY` is a sure sign of a flaw. The fact you treat `DISTINCT` like a function another. Also why are you grouping on one of th few columns you're aggregating on? — Thom A, Mar 10 '22 at 17:52
The fact that you don't qualify *any* of your columns also makes it impossible for us to know if those `LEFT JOIN`s are needed, and every single one of your CTEs also have an expensive `DISTINCT` operator. Are you, perhaps, unaware that `DISTINCT` is optional? It's a very expensive operator and should be used sparingly. It's most certainly being misused a lot in the above. I would suggest taking a step back and provide sample data (in a consumable format) expected results and a description of the goal here. — Thom A, Mar 10 '22 at 17:56
Where's the rest of the query? None of the CTE query definitions (nor their columns) are referenced by the consuming query. Where do those extra columns in the consuming query come from? — AlwaysLearning, Mar 11 '22 at 04:53
@Igor I can't look at Execution Plan as never had my query executed. It took 2 hours and then I stopped query. Don't know what can I do... It runs too long — AnnaSh, Mar 11 '22 at 09:52
2 hours run and still not finished and this is without DISTINCT — AnnaSh, Mar 11 '22 at 09:54
You can still get the estimated execution plan or the actual one if you profile the sql instance on event rpc starting and log the execution plan column. — Igor, Mar 11 '22 at 13:22

Karn Ratana · Answer 1 · 2022-03-11T16:06:10.213

I'm taking a stab at it without more information. I converted the CTE into OUTER APPLY. You might need to tweak it a bit for the cases when OptIn=0. Give it a try and see if the performance is any better.

SELECT distinct c.[ScvId] 
    ,Title
    ,FirstName
    ,LastName
    ,EmailAddress
    ,DateOfBirth
    ,Address1
    ,City
    ,Telephone
    ,Gender
    ,URC_OptIn
    ,Partner_OptIn
    ,Leinster_OptIn
    ,MAX(CASE WHEN c.ScvId = T.ScvId AND ProductId = '2019' THEN 1 ELSE 0 END) AS FinalTicketPurchaser_2019
    ,MAX(CASE WHEN c.ScvId = T.ScvId AND ProductId = '2018' THEN 1 ELSE 0 END) AS FinalTicketPurchaser_2018
FROM scv.vwCustomer c   
    OUTER APPLY (
        SELECT TOP 1 OptIn AS URC_OptIn
        FROM scv.OptIn oiu
        WHERE oiu.scvid = c.Scvid AND oiu.OptIn = '1' AND oiu.who = 'urc' 
    ) AS U
    OUTER APPLY (
        SELECT TOP 1 OptIn AS Partner_OptIn
        FROM scv.OptIn ois
        WHERE ois.scvid = c.Scvid AND ois.OptIn = '1' AND ois.who = 'sponsor/3rd party' 
    ) AS S
    OUTER APPLY (
        SELECT TOP 1 OptIn AS Leinster_OptIn
        FROM scv.OptIn oil
        WHERE oil.scvid = c.Scvid AND oil.OptIn = '1' AND oil.who = 'leinster rugby'
    ) AS L
    LEFT JOIN vwAggTransaction T ON c.ScvId = T.ScvId
GROUP BY c.[ScvId]
    Title,
    FirstName,
    LastName,
    EmailAddress,
    DateOfBirth,
    Address1,
    City,
    Telephone,
    Gender,
    URC_OptIn,
    Partner_OptIn,
    Leinster_OptIn,
ORDER BY c.scvid

Thanks for pointing that out. I copied that part straight from the original query. It's been updated. — Karn Ratana, Mar 11 '22 at 16:06

SQL Server - Query with MAX(CASE ..) and GROUP BY runs too long

1 Answers1