在 WhereClause 中使用变量时增加过程的执行持续时间

Question

我在SQL Server 2008 R2中执行了一个过程，脚本为：

DECLARE @LocalVar SMALLINT = GetLocalVarFunction();
SELECT 
  [TT].[ID],
  [TT].[Title]
FROM [TargetTable] AS [TT]
LEFT JOIN [AcceccTable] AS [AT] ON [AT].[AccessID] = [TT].[ID]
WHERE 
(
  (@LocalVar = 1 AND ([AT].[Access] = 0 OR [AT].[Access] Is Null) AND
  ([TT].[Level] > 7)
);
GO

此过程在 16 秒内执行。但是当我将 Where 子句更改为：

  WHERE 
(
  ((1=1) AND [AT].[Access] = 0 OR [AT].[Access] Is Null) AND
  ([TT].[Level] > 7)
);

该过程在不到1秒的时间内执行。

如您所见，我只是删除了局部变量。

那么问题出在哪里呢？在 where 子句中使用局部变量我缺少什么吗？当我在 where 子句中使用局部变量时，有什么建议可以改善执行时间吗？

更新：

我还想在脚本之前添加一个 if 语句并将过程拆分为 2 个过程，但我有 4 或 5 个像上面这样的变量，并且使用if语句非常复杂。

更新2：

我更改了@LocalVar集：

DECLARE @LocalVar SMALLINT = 1;

执行时间没有变化。

Answer 1

当您在过滤器中使用使用局部变量WHERE则会导致全表扫描。在编译时，SQL Server 不知道局部变量的值。因此，SQL Server 为可用于该列的最大规模创建执行计划。

如您所见，当您传递1==1时，SQL Server知道该值，因此性能不会降低。但是当你传递一个局部变量时，这个值是未知的。

一种解决方案可能是在SQL查询结束时使用选项（重新编译）

您可以查看针对未知进行优化

Answer 2

当您在 WHERE 中使用局部变量时，优化器不知道如何处理它。

您可以查看此链接

在这种情况下，您可以做的是运行查询，在两种情况下显示实际计划，并查看SQL如何处理它们。

Answer 3

您似乎将@LocalVar用作分支条件，如下所示：

• 如果@LocalVar为 1，则对查询应用筛选器
• 如果 @LocalVar 为 0，则返回一个空结果集。

IMO 您最好明确地编写此条件，因为这样 SQL 将能够优化 2 个分支的单独计划，即

DECLARE @LocalVar SMALLINT = GetLocalVarFunction();
IF (@LocalVar = 1)
    SELECT 
      [TT].[ID],
      [TT].[Title]
    FROM [TargetTable] AS [TT]
    LEFT JOIN [AcceccTable] AS [AT] ON [AT].[AccessID] = [TT].[ID]
    WHERE 
    (
      ([AT].[Access] = 0 OR [AT].[Access] Is Null) AND
      ([TT].[Level] > 7)
    )
ELSE
    SELECT 
      [TT].[ID],
      [TT].[Title]
    FROM [TargetTable] AS [TT]
    WHERE 1=2 -- Or any invalid filter, to retain the empty result

然后，由于现在有 2 个分支通过存储过程，因此应向存储过程添加WITH RECOMPILE，因为 2 个分支具有完全不同的查询计划。

编辑

只是为了澄清评论：

请注意，将OPTION(RECOMPILE)放在查询之后意味着永远不会缓存查询计划 - 如果频繁调用查询，这可能不是一个好主意。

PROC 级别的WITH RECOMPILE可防止通过 proc 缓存分支。它与查询级别的OPTION(RECOMPILE)不同。

如果您的查询中有大量过滤器排列，那么上面的"分支"技术不能很好地扩展 - 您的代码很快就会变得不可维护。

不幸的是，您可能需要考虑使用参数化的动态 SQL。然后，SQL 将至少为每个排列缓存一个单独的计划。