sql-server - SQL Server 查询优化 : Where (Col=@Col or @Col=Null)

Question

不确定从哪里开始 - 不确定问题是我在愚弄查询优化器，还是涉及空值时索引工作方式的固有问题。

我遵循的一个编码惯例是像这样编写存储过程:

declare procedure SomeProc
  @ID int = null
as
  select
    st.ID,st.Col1,st.Col2
  from
    SomeTable st
  where
    (st.ID = @ID or @ID is null) --works, but very slow (relatively)

当然，在那个简单的测试用例中不是很有用，但在您希望存储过程作用于整个表或满足某些条件的行的其他场景中很有用。但是，在更大的表上使用时速度非常慢……比我将 where 子句替换为以下内容大约慢 3-5 倍:

where
    st.ID = @ID --3-5x faster than first example

我更困惑的是，用 -1 替换 null 给我的速度几乎与上面的“固定”WHERE 子句相同:

declare procedure SomeProc
  @ID int = -1
as
  select
    st.ID,st.Col1,st.Col2
  from
    SomeTable st
  where
    (st.ID = @ID or @ID=-1) --much better... but why?

显然是 null 使事情变得古怪，但究竟为什么呢？通过检查执行计划，我不清楚答案。这是我多年来在各种数据库、表和 SQL Server 版本上注意到的事情，所以我认为这不是我当前环境的怪癖。 我通过将默认参数值从 null 切换为 -1 解决了这个问题；我的问题是为什么这行得通。

注释

1. SomeTable.ID 已编入索引
2. 它可能与(或者实际上可能是)参数嗅探问题有关 Parameter Sniffing (or Spoofing) in SQL Server不管它值多少钱，我一直在几乎完全测试在每个之后“执行 SomeProc”编辑/重新编译 proc，即省略了可选参数。

Answer 1

您很可能遇到了一系列问题

1. 参数嗅探
2. OR 不是一个好用的运算符

但在没有看到计划的情况下，这些都是有根据的猜测。

参数嗅探

...默认为“NULL”。尝试使用不同的默认值，例如 -1 或无默认值。

默认为 NULL 的 @ID = -1 和参数 sniffing = trivial check，所以速度更快。

你也可以试试 OPTIMISE FOR UNKNOWN在 SQL Server 2008 中

OR 运算符

一些想法..

如果列不可为空，在大多数情况下优化器会忽略该条件

st.ID = ISNULL(@ID, st.ID)

此外，您还可以使用 IF 语句

IF @ID IS NULL
   SELECT ... FROM...
ELSE
   SELECT ... FROM... WHERE st.ID

或以类似的方式联合所有。

就个人而言，在大多数情况下我会(总是)使用参数屏蔽和 ISNULL(我会先尝试)

alter procedure SomeProc
  @ID int = NULL
AS
declare @maskID int
select @maskID = @ID
...