sql-server - T-SQL - 使用非最优计划 - WHERE 子句应短路

Question

我们有许多“搜索存储过程”，它们采用多个可为空的参数来搜索不同表中的数据行。它们通常是这样构建的:

SELECT      *
 FROM       Table1 T1
 INNER JOIN Table2 T2
     ON     T2.something = T1.something
 WHERE      (@parameter1 IS NULL OR T1.Column1 = @parameter1)
        AND (@parameter2 IS NULL OR T2.Column2 = @parameter2)
        AND (@parameter3 IS NULL OR T1.Column3 LIKE '%' + @parameter3 + '%')
        AND (@parameter4 IS NULL OR T2.Column4 LIKE '%' + @parameter4 + '%')       
        AND (@parameter5 IS NULL OR T1.Column5 = @parameter5)

这最多可以持续 30-40 个参数，我们注意到，即使只提供了参数 1，执行计划也会通过其他表的索引扫描，这会显着减慢查询速度(几秒钟) 。测试表明，仅保留 WHERE 语句的第一行可以使查询即时进行。

1. 我了解到捷径是不可能的，但是是否有解决方法或方法来构造可能更有效的查询？

2. 我们目前通过使用相同 SELECT/FROM/JOINS 的不同版本但在 WHERE 子句中使用不同的参数集来解决此问题，并且根据传递的参数，我们选择要执行的正确 select 语句。这是冗长、困惑且难以维护的。

Answer 1

SQL Server 中的查询计划经过编译和存储以供重用。即使 SQL Server 发现您的参数为 null，它也必须提出一个适用于参数非 null 情况的查询计划。

查询提示选项(重新编译)是在 SQL Server 2005 中引入的，但直到 SQL Server 2008 才真正对您此处的查询类型产生影响。

每次重新编译查询时，它都不会存储在查询计划缓存中，因此 SQL Server 可以自由地优化针对 null 参数的检查。

在此处了解更多信息 Dynamic Search Conditions in T-SQL

您可以测试一些示例代码以查看查询计划的差异。对 SP 的第一次调用将执行索引查找，第二次调用将执行聚集索引扫描。

create table T
(
  ID int identity primary key,
  Col1 int,
  Col2 int
);

go

create index IX_T on T(Col1);

go

create procedure GetT
  @Col1 int,
  @Col2 int
as

select ID
from T
where (Col1 = @Col1 or @Col1 is null) and
      (Col2 = @Col2 or @Col2 is null)
option (recompile);

go

exec GetT 1, null
exec GetT 1, 1