分配给变量时的sql执行延迟

Question

以下查询将在约22秒内运行：

DECLARE @i INT, @x INT
SET    @i = 156567

SELECT 
TOP 1
    @x = AncestorId
FROM 
    dbo.tvw_AllProjectStructureParents_ChildView a
WHERE 
    ProjectStructureId = @i AND
        a.NodeTypeCode = 42 AND
        a.AncestorTypeDiffLevel = 1
OPTION (RECOMPILE)

问题出在 变量赋值上 （实际上是这一行：）@x = AncestorId。删除作业时，它最多可以加快15毫秒！我通过将结果插入到临时表中解决了这个问题，但我认为这是一种不好的方法。

任何人都可以帮助我解决问题的根源吗？

聚苯乙烯

错误的执行计划（22 s
）：https :
//www.brentozar.com/pastetheplan/?id=Sy6a4c9bW

良好的执行计划（20 毫秒
）：https :
//www.brentozar.com/pastetheplan/?id=Byg8Hc5ZZ

Answer 1

使用OPTION (RECOMPILE)SQL
Server时，通常可以执行参数嵌入优化。

它正在编译的计划是一次性的，因此它可以嗅探所有变量和参数的值并将其视为常量。

下面是一个简单的示例，该示例显示了实际的参数嵌入优化以及分配给变量的效果（未估算实际执行计划）。

DECLARE @A INT = 1, 
        @B INT = 2,
        @C INT;

SELECT TOP (1) number FROM master..spt_values WHERE @A > @B;
SELECT TOP (1) number FROM master..spt_values WHERE @A > @B OPTION (RECOMPILE);
SELECT TOP (1) @C = number FROM master..spt_values WHERE @A > @B OPTION (RECOMPILE);

计划如下

请注意，中间的一个甚至根本不涉及该表，因为SQL Server可以在编译时推导出该表，而事实@A > @B并非如此true。但是计划3回到表中，因为变量分配显然阻止了OPTION (RECOMPILE)计划2所示的效果。

（顺便说一句，第三个计划实际上并不比第一个计划贵4-5倍。分配给变量似乎也抑制了通常的行目标逻辑，在该逻辑中，索引扫描的成本将按比例缩小以反映TOP 1）

在您的好计划中，将的@i值直接156567推入递归CTE锚点中的seek中，它返回了0行，因此递归部分无需执行任何操作。

在您的错误计划中，递归CTE会完全实现627,393次递归子树的执行，最后将谓词应用于最后的627,393行（丢弃所有行）

我不确定为什么SQL Server无法将带有变量的谓词下推。您尚未提供表的定义-
或带有递归CTE的视图。但是，谓词推送，视图和窗口函数也存在类似的问题。

一种解决方案是将视图更改为内联表值函数，该函数接受mainid参数，然后将其添加到WHERE定义的锚定部分的子句中。而不是依靠SQL
Server为您降低谓词。