sql - 使用 CTE 优化时态表

Question

我创建临时表以设置级别:

CREATE TABLE [#DesignLvl]
(
    [DesignKey] INT,
    [DesignLevel] INT
);

WITH RCTE AS 
(
    SELECT
        *,
        1 AS [Lvl]
    FROM 
        [Design]
    WHERE 
        [ParentDesignKey] IS NULL

    UNION ALL

    SELECT
        [D].*,
        [Lvl] + 1 AS [Lvl]
    FROM 
        [dbo].[Design] AS [D]
    INNER JOIN 
        [RCTE] AS [rc] ON [rc].[DesignKey] = [D].[ParentDesignKey]
)
INSERT INTO [#DesignLvl]
    SELECT
        [DesignKey], [Lvl]
    FROM 
        [RCTE]

创建后，我在非常大的查询中用作 LEFT JOIN:

SELECT... 
FROM.. 
LEFT JOIN [#DesignLvl] AS [dl] ON d.DesignKey = dl.DesignKey
WHERE ...

查询可以工作，但性能下降了，而且查询现在太慢了。有什么办法可以优化这个表吗？

CTE执行计划

我尝试将聚集索引添加为:

CREATE TABLE [#DesignLvl]
(
    [DesignKey] INT,
    [DesignLevel] INT
);

CREATE CLUSTERED INDEX ix_DesignLvl 
    ON [#DesignLvl] ([DesignKey], [DesignLevel]);

也可以尝试:

    CREATE TABLE [#DesignLvl] 
( [DesignKey] INT INDEX IX1 CLUSTERED ,
 [DesignLevel] INT INDEX IX2 NONCLUSTERED );

但我得到了相同的结果，执行花了很长时间

Answer 1

性能可能会降低，因为在嵌套循环内访问 dbo.Design 表上的聚集索引。根据成本估算，数据库花费了 66% 的时间来扫描该索引。循环只会让情况变得更糟。

参见related question

考虑将 dbo.Design 上的索引更改为非聚集索引，或者尝试使用非聚集索引创建另一个临时表并将其用于递归查询:

CREATE TABLE [#DesignTemp]
(
    ParentDesignKey INT,
    DesignKey INT
);

-- Insert the data, then create the index.
INSERT INTO [#DesignTemp]
SELECT
ParentDesignKey,
DesignKey
FROM [dbo].[Design];

COMMIT;

-- Try this index, or create indexes for individual columns if the plan works better at high volumes.
CREATE NONCLUSTERED INDEX ix_DesignTemp1 ON [#DesignTemp] (ParentDesignKey, DesignKey);

CREATE TABLE [#DesignLvl]
(
    [DesignKey] INT,
    [DesignLevel] INT
);

WITH RCTE AS 
(
    SELECT
        *,
        1 AS [Lvl]
    FROM 
        [DesignTemp]
    WHERE 
        [ParentDesignKey] IS NULL

    UNION ALL

    SELECT
        [D].*,
        [Lvl] + 1 AS [Lvl]
    FROM 
        [DesignTemp] AS [D]
    INNER JOIN 
        [RCTE] AS [rc] ON [rc].[DesignKey] = [D].[ParentDesignKey]
)
INSERT INTO [#DesignLvl]
    SELECT
        [DesignKey], [Lvl]
    FROM 
        [RCTE];