主页 / dba / 问题 / 246734
Accepted
binki
Asked: 2019-09-02 22:29:54 +0800 CST

为什么这个带有参数的递归 CTE 在使用文字时不使用索引？

  • 772

我在树结构上使用递归 CTE 来列出树中特定节点的所有后代。如果我在我的WHERE子句中写一个文字节点值，SQL Server 似乎实际上只将 CTE 应用于该值，从而给出一个实际行数较低的查询计划，等等：

具有文字值的查询计划

但是，如果我将该值作为参数传递，它似乎会实现（假脱机）CTE，然后在事后对其进行过滤：

带参数值的查询计划

我可能读错了计划。我没有注意到性能问题，但我担心 CTE 的实现可能会导致更大的数据集出现问题，尤其是在更繁忙的系统中。此外，我通常在自身上复合这种遍历：我向上遍历祖先并返回到后代（以确保我收集所有相关节点）。由于我的数据是这样的，每组“相关”节点都相当小，因此实现 CTE 没有意义。当 SQL Server 似乎实现了 CTE 时，它的“实​​际”计数给了我一些相当大的数字。

有没有办法让查询的参数化版本像文字版本一样工作？我想将 CTE 放在可重用的视图中。

用文字查询：

CREATE PROCEDURE #c AS BEGIN;
    WITH descendants AS (SELECT
         t.ParentId Id
        ,t.Id DescendantId
    FROM #tree t
    WHERE t.ParentId IS NOT NULL
    UNION ALL SELECT
         d.Id
        ,t.Id DescendantId
    FROM descendants d
    JOIN #tree t ON d.DescendantId = t.ParentId)
    SELECT d.*
    FROM descendants d
    WHERE d.Id = 24
    ORDER BY d.Id, d.DescendantId;
END;
GO
EXEC #c;

带参数查询：

CREATE PROCEDURE #c (@Id BIGINT) AS BEGIN;
    WITH descendants AS (SELECT
         t.ParentId Id
        ,t.Id DescendantId
    FROM #tree t
    WHERE t.ParentId IS NOT NULL
    UNION ALL SELECT
         d.Id
        ,t.Id DescendantId
    FROM descendants d
    JOIN #tree t ON d.DescendantId = t.ParentId)
    SELECT d.*
    FROM descendants d
    WHERE d.Id = @Id
    ORDER BY d.Id, d.DescendantId;
END;
GO
EXEC #c 24;

设置代码：

DECLARE @count BIGINT = 100000;
CREATE TABLE #tree (
     Id BIGINT NOT NULL PRIMARY KEY
    ,ParentId BIGINT
);
CREATE INDEX tree_23lk4j23lk4j ON #tree (ParentId);
WITH number AS (SELECT
         CAST(1 AS BIGINT) Value
    UNION ALL SELECT
         n.Value * 2 + 1
    FROM number n
    WHERE n.Value * 2 + 1 <= @count
    UNION ALL SELECT
         n.Value * 2
    FROM number n
    WHERE n.Value * 2 <= @count)
INSERT #tree (Id, ParentId)
SELECT n.Value, CASE WHEN n.Value % 3 = 0 THEN n.Value / 4 END
FROM number n;
sql-server optimization

2 个回答

  1. Best Answer

    Randi Vertongen 的回答正确地解决了如何使用查询的参数化版本获得所需的计划。如果您对细节感兴趣，该答案通过解决问题的标题来补充这一点。

    SQL Server 将尾递归公用表表达式 (CTE) 重写为迭代。从惰性索引假脱机向下的所有内容都是迭代翻译的运行时实现。我详细说明了执行计划的这一部分是如何工作的，以回答Using EXCEPT in a recursive common table expression。

    您想在 CTE之外指定一个谓词（过滤器） ，并让查询优化器将此过滤器下推到递归内部（重写为迭代）并将其应用于锚成员。这将意味着递归仅从匹配的那些记录开始ParentId = @Id。

    这是一个相当合理的期望，无论是使用文字值、变量还是参数；但是，优化器只能执行已为其编写规则的事情。规则指定如何修改逻辑查询树以实现特定转换。它们包括确保最终结果安全的逻辑 - 即它在所有可能的情况下返回与原始查询规范完全相同的数据。

    负责在递归 CTE 上推送谓词的规则称为SelOnIterator- 实现递归的迭代器上的关系选择（= 谓词）。更准确地说，这个规则可以将选择复制到递归迭代的锚部分：

    Sel(Iter(A,R)) -> Sel(Iter(Sel(A),R))

    可以使用未记录的提示禁用此规则OPTION(QUERYRULEOFF SelOnIterator)。使用此选项时，优化器不能再将具有文字值的谓词下推到递归 CTE 的锚点。你不希望这样，但它说明了这一点。

    最初，此规则仅限于处理具有文字值的谓词。它也可以通过指定来处理变量或参数OPTION (RECOMPILE)，因为该提示启用了参数嵌入优化，从而在编译计划时使用变量（或参数）的运行时文字值。该计划没有被缓存，因此它的缺点是每次执行时都要重新编译。

    在某些时候，该SelOnIterator规则得到了改进，也适用于变量和参数。为了避免意外的计划更改，这受到 4199 跟踪标志、数据库兼容性级别和查询优化器修补程序兼容性级别的保护。这是优化器改进的正常模式，并不总是记录在案。改进通常对大多数人都有好处，但任何改变都有可能给某人带来倒退。

    我想将 CTE 放在可重复使用的视图中

    您可以使用内联表值函数而不是视图。提供要下推的值作为参数，并将谓词放在递归锚成员中。

    如果您愿意，也可以选择全局启用跟踪标志 4199。此标志涵盖了许多优化器更改，因此您需要在启用它的情况下仔细测试您的工作负载，并准备好处理回归。

    • 12

  2. 虽然目前我没有实际修补程序的标题，但在您的版本（SQL Server 2012）上启用查询优化器修补程序时将使用更好的查询计划。

    其他一些方法是：

    • 使用OPTION(RECOMPILE)so 过滤发生在更早的文字值上。
    • 在 SQL Server 2016 或更高版本上，此版本之前的修补程序会自动应用，并且查询的运行也应等同于更好的执行计划。

    查询优化器修补程序

    您可以启用这些修复

    • SQL Server 2016 之前的 Traceflag 4199
    • ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES=ON;从 SQL Server 2016 开始。（您的修复不需要）

    在启用了修补程序的情况下，过滤@id器较早应用于执行计划中的递归和锚定成员。

    可以在查询级别添加跟踪标志：

    OPTION(QUERYTRACEON 4199)

    在 SQL Server 2012 SP4 GDR 或 SQL Server 2014 SP3 上运行带有 Traceflag 4199 的查询时，会选择更好的查询计划：

    ALTER PROCEDURE #c (@Id BIGINT) AS BEGIN;
    WITH descendants AS (SELECT
         t.ParentId Id
        ,t.Id DescendantId
    FROM #tree t 
    WHERE t.ParentId IS NOT NULL
    UNION ALL 
    SELECT
         d.Id
        ,t.Id DescendantId
    FROM descendants d
    JOIN #tree t ON d.DescendantId = t.ParentId)
    SELECT d.*
    FROM descendants d
    WHERE d.Id = @Id
    ORDER BY d.Id, d.DescendantId
    OPTION( QUERYTRACEON 4199 );

END;
GO
EXEC #c 24;

    SQL Server 2014 SP3 上的查询计划，带有 traceflag 4199

    带有 traceflag 4199 的 SQL Server 2012 SP4 GDR 上的查询计划

    没有跟踪标志 4199 的 SQL Server 2012 SP4 GDR 上的查询计划

    主要共识是在使用SQL Server 2016之前的版本时全局启用traceflag 4199。之后是否启用它是开放的讨论。AQ/A 关于这里。

    兼容级别 130 或 140

    在 = 130 或 140 的数据库上测试参数化查询时compatibility_level，过滤发生得更早：

    在此处输入图像描述

    由于在 SQL Server 2016 及更高版本上启用了来自 traceflag 4199 的“旧”修复。

    选项（重新编译）

    即使使用了过程，SQL Server 也可以在添加OPTION(RECOMPILE);.

    ALTER PROCEDURE #c (@Id BIGINT) AS BEGIN;
    WITH descendants AS (SELECT
         t.ParentId Id
        ,t.Id DescendantId
    FROM #tree t 
    WHERE t.ParentId IS NOT NULL
    UNION ALL 
    SELECT
         d.Id
        ,t.Id DescendantId
    FROM descendants d
    JOIN #tree t ON d.DescendantId = t.ParentId)
    SELECT d.*
    FROM descendants d
    WHERE d.Id = @Id
    ORDER BY d.Id, d.DescendantId
OPTION(
RECOMPILE )

END;
GO

    在此处输入图像描述

    带有 OPTION(RECOMPILE) 的 SQL Server 2012 SP4 GDR 上的查询计划

    • 10

相关问题