sql - 在 DELETE 中使用 CTE 未执行

Question

这是运行并运行的原始查询:

;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     
                    )   

如果我将上面的更改为下面，那么它会在不到 10 秒的时间内运行。为什么这些执行如此不同？

;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     
        )   

Answer 1

CTE 在使用时被解析。这意味着如果您在查询中使用它两次，它可能会被解析两次。 CTE做并非总是解决一次并缓存在内存中。 SQL Server 可以自由地执行它认为合适的查询。

在您的情况下，它可能比这更糟糕 - 因为您已将它用作 EXISTS 子句中的相关子查询，这是逐行操作。这可能意味着该计划导致 的 CTE 得到解决。每一行 #permissions 表!这很可能是所有时间都会去的地方。在 SSMS 中显示执行计划 (Ctrl-L) 是您的 friend 。

检查这个 SQLFiddle ，这表明没有一个 GUID 是相同的，即使 CTE 只创建了 3 行。事实上，我们得到了 18 个不同的 GUID。

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;