Using CTE in a DELETE is not executing

Question

Here is the original query that runs and runs:

;WITH includedCs_cte
    AS
    (
    SELECT 
            x.PKey, 
            x.OKey,
            y.CKey 
    FROM  
            #Permissions x
            JOIN WHDATA.dbo.tb_DimC y
                    ON 
                    x.PKey = y.PKey AND
                    x.OKey = y.OKey
        )
, b_cte
    AS
    (
    SELECT
            i.OKey,
            i.PKey
    FROM 
                WHData.dbo.vw_FactCX b
                INNER JOIN includedCs_cte i
                        ON 
                        b.PKey = i.PKey AND
                        b.PlayCKey = i.CKey
    WHERE b.DateKey >= @myLAST28DAYS
    GROUP BY
            i.OKey,
            i.PKey
    )
, POK_cte
    AS
    (
    SELECT
            i.OKey,
            i.PKey
    FROM 
                WHData.dbo.vw_FactCY b
                INNER JOIN includedCs_cte i
                        ON 
                        b.PKey = i.PKey AND
                        b.PlayCKey = i.CKey
    WHERE b.DateKey >= @myLAST28DAYS
    GROUP BY
            i.OKey,
            i.PKey
    )
, includedOKeys
    AS
    (
    SELECT *
    FROM b_cte
    UNION
    SELECT * FROM POK_cte
    )
DELETE FROM #Permissions
FROM #Permissions p
WHERE NOT EXISTS
                    (
                    SELECT 1
                    FROM includedOKeys x
                    WHERE 
                            p.PKey = x.PKey AND
                            p.OKey = x.OKey     
                    )

If I change the above to the below then it runs in less than 10 seconds. Why are these executing so differently?

;WITH includedCs_cte
    AS
    (
    SELECT 
            x.PKey, 
            x.OKey,
            y.CKey 
    FROM  
            #Permissions x
            JOIN WHDATA.dbo.tb_DimC y
                    ON 
                    x.PKey = y.PKey AND
                    x.OKey = y.OKey
        )
, b_cte
    AS
    (
    SELECT
            i.OKey,
            i.PKey
    FROM 
                WHData.dbo.vw_FactCX b
                INNER JOIN includedCs_cte i
                        ON 
                        b.PKey = i.PKey AND
                        b.PlayCKey = i.CKey
    WHERE b.DateKey >= @myLAST28DAYS
    GROUP BY
            i.OKey,
            i.PKey
    )
, POK_cte
    AS
    (
    SELECT
            i.OKey,
            i.PKey
    FROM 
                WHData.dbo.vw_FactCY b
                INNER JOIN includedCs_cte i
                        ON 
                        b.PKey = i.PKey AND
                        b.PlayCKey = i.CKey
    WHERE b.DateKey >= @myLAST28DAYS
    GROUP BY
            i.OKey,
            i.PKey
    )
, includedOKeys
    AS
    (
    SELECT *
    FROM b_cte
    UNION
    SELECT * FROM POK_cte
    )
SELECT *
INTO #includedOKeys
FROM includedOKeys
CREATE CLUSTERED INDEX ix_inclProdOper ON #includedOKeys(OKey, PKey)

DELETE FROM #Permissions
FROM #Permissions p
WHERE NOT EXISTS
        (
        SELECT 1
        FROM #includedOKeys x
        WHERE 
            p.PKey = x.PKey AND
            p.OKey = x.OKey     
        )

Execution plan can reveal the mystery, and most probably because SQL create variable table in memory for CTE where as in second one you create temporary table for it. — Farfarak
– Farfarak, Commented Oct 25, 2012 at 14:11
The specific answer here depends upon how different these perform if you don't create the clustered index in your second example. Beyond that, there is a lot of info available if you search for "temp table vs table variable vs cte" or something similar. — Tim Lehner
– Tim Lehner, Commented Oct 25, 2012 at 14:24
@010001100110000101110010011010, table variables may be written to tempdb as well. — Tim Lehner
– Tim Lehner, Commented Oct 25, 2012 at 14:28

RichardTheKiwi · Accepted Answer · 2012-10-25 14:46:01Z

3

A CTE is resolved when it is used. It means that if you use it twice in a query, it may get resolved twice. CTEs do not always get resolved once and cache in memory. SQL Server is free to execute the query as it sees fit.

In your case, it may be worse than that - because you have used it as a correlated subquery in an EXISTS clause, which is a row-by-row operation. This probably means the plan results in the CTE being resolved for EACH ROW of the #permissions table! That could well be where all the time will be going. Show Execution Plan (Ctrl-L) in SSMS is your friend here.

Check this SQLFiddle, which shows that NONE of the GUIDs are the same, even though the CTE only creates 3 rows. In fact, we get 18 distinct GUIDs.

with cte(guid,other) as (
  select newid(),1 union all
  select newid(),2 union all
  select newid(),3)
select a.guid, a.other, b.guid guidb, b.other otherb
from cte a
cross join cte b
order by a.other, b.other;

edited Oct 25, 2012 at 14:46

answered Oct 25, 2012 at 14:29

RichardTheKiwi

108k28 gold badges206 silver badges269 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

ypercubeᵀᴹ Over a year ago

But they aren't always resolved twice either. Another SQL-Fiddle (with 40 instead of 3) with a different execution plan (and not 2x40x40 different GUIDs).

RichardTheKiwi Over a year ago

True, edited the answer. I didn't mention where it doesn't do that, as I was gearing the answer towards what is probably happening to the OP's query.

whytheq Over a year ago

if I amend the query so it uses a LEFT OUTER JOIN instead of NOT EXISTS then it still hangs ...wouldn't have thought that a LEFT OUTER JOIN was row-by-row ?

RichardTheKiwi Over a year ago

Have you seen my query? It's a cross join. Each row in the former was materialized against each row of the other. Which caused both sides to re-render all rows independently. Like I said, no two guids were the same. Have you tried Show Execution Plan? You can edit the image into the question

Collectives™ on Stack Overflow

Using CTE in a DELETE is not executing

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related