sql-server - 检查变量是否为 NULL 会降低性能

Question

我有以下疑问:

DECLARE @application_number CHAR(8)= '37832904';
SELECT
    la.LEASE_NUMBER AS lease_number,
    la.[LEASE_APPLICATION] AS application_number,
    tnu.[FOLLOWUP_CODE] AS note_type_code -- catch codes not in codes table
FROM [dbo].[lease_applications] la
LEFT JOIN [dbo].tickler_notes_uniq tnu ON tnu.[ACCOUNT_NUMBER] = la.[ACCOUNT_NUMBER]
WHERE la.LEASE_APPLICATION = @application_number
      OR @application_number IS NULL;



SELECT
    la.LEASE_NUMBER AS lease_number,
    la.[LEASE_APPLICATION] AS application_number,
    tnu.[FOLLOWUP_CODE] AS note_type_code -- catch codes not in codes table
FROM [dbo].[lease_applications] la
LEFT JOIN [dbo].tickler_notes_uniq tnu ON tnu.[ACCOUNT_NUMBER] = la.[ACCOUNT_NUMBER]
WHERE la.LEASE_APPLICATION = @application_number; 

这两个查询之间的唯一区别是我添加了对变量是否为 NULL 的检查。

这些查询的执行计划是:

您可以找到图形计划here

所以问题是。为什么计划如此不同？

更新:

第一个查询的实际执行计划可以查看 here

OPTION(RECOMPILE) 将实际的执行计划更改为好的执行计划。然而，这样做的缺点是我的主要目标是使用这些参数创建 TVF，然后每个使用该函数的人都应该提供该选项。

还值得一提的是，我的主要目标是创建具有 2 个参数的 TVF。其中每个都可能为空，也可能不为空，但至少其中 1 个应该为 NOT NULL。这些参数或多或少是相等的，它们只是两个表中的不同键，无论如何都会给出相同的结果(相同的行数等等)。这就是为什么我想做类似的事情

WHERE (col1 = @param1 OR @param1 IS NULL) AND (col2 = @param2 OR @param2 IS NULL) AND (@param1 IS NOT NULL or @param2 IS NOT NULL)

所以，基本上我对所有记录根本不感兴趣

Answer 1

您对两个不同的查询有两个不同的计划。这是有道理的，当您在 WHERE 子句(la.LEASE_APPLICATION = @application_number)上有相等条件时(并且有索引)，您会得到一个 索引寻找:按预期工作!

另一方面，当您将两个条件写入一个 WHERE 子句时 (la.LEASE_APPLICATION = @application_number OR @application_number IS NULL) 查询优化器会选择进行扫描。

即使参数值已提供且不为空，但正在使用的计划是缓存的计划，它无法在编译时知道参数的实际值。

如果您有一个存储过程并且使用参数调用它，就会出现这种情况。使用变量执行简单查询时情况并非如此。正如 @sepupic 所说，变量值不会被嗅探。

生成的计划可以处理两种情况:当您有参数值时以及没有参数值时。

解决问题的一个选项是使用OPTION(RECOMPILE)，因为它已经在评论中说明了。另一种选择是将您的查询分开(例如，有两个不同的存储过程，由第三个“包装器”过程调用)，以便它们得到相应的优化，每个查询都是独立的。

我建议您看一下 Kimberly L. Tripp 的这篇文章:Building High Performance Stored Procedures另一个作者是 Aaron Bertrand:An Updated "Kitchen Sink" Example 。我认为这些是解释此类场景的最佳文章。

这两篇文章都解释了这种情况、可能出现的问题以及可能的解决方案，例如选项(重新编译)、动态sql或使用单独的存储过程。

祝你好运!