SQL Server 查询优化:其中(Col=@Col 或 @Col=Null)

问题描述：

不知道从哪里开始——不知道问题是我在愚弄查询优化器，还是当涉及空值时索引的工作方式是固有的.

Not sure where to start on this one -- not sure if the problem is that I'm fooling the query optimizer, or if it's something intrinsic to the way indexes work when nulls are involved.

我遵循的一种编码约定是对存储过程进行编码，如下所示:

One coding convention I've followed is to code stored procedures like such:

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 倍:

Not very useful in that simple test case, of course, but useful in other scenarios when you want a stored proc to act on either the entire table OR rows that meet some criteria. However, that's quite slow when used on bigger tables... roughly 3-5x slower than if I replaced the where clause with:

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

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

I'm even more puzzled by the fact that replacing the null with -1 gives me nearly the same speed as that "fixed" WHERE clause above:

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 解决了这个问题；我的问题是为什么这有效.

Clearly it's the null that's making things wacky but why, exactly? The answer is not clear to me from examining the execution plan. This is something I've noticed over the years on various databases, tables, and editions of SQL Server so I don't think it's a quirk of my current environment. I've resolved the issue by switching the default parameter value from null to -1; my question is why this works.

注意事项

SomeTable.ID 已编入索引
它可能与(或者实际上可能是)参数嗅探问题有关SQL Server 中的参数嗅探(或欺骗)不管它值多少钱，我一直测试几乎完全与在每个之后执行 SomeProc"编辑/重新编译过程，即省略了可选参数.

SomeTable.ID is indexed
It may be related to (or may, in fact, be) a parameter sniffing issue Parameter Sniffing (or Spoofing) in SQL Server For whatever it's worth, I've been testing almost exclusively with "exec SomeProc" after each edit/recompile of the proc, ie, with the optional parameter omitted.

答

您有多种问题，很可能

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

但没有看到计划，这些都是有根据的猜测.

But without seeing the plans, these are educated guesses.

参数嗅探

... 的默认NULL".尝试使用不同的默认值，例如 -1 或无默认值.

... of the default "NULL". Try it with different defaults, say -1 or no default.

@ID = -1，默认为 NULL，参数嗅探 = 简单检查，所以速度更快.

The @ID = -1 with a default of NULL and parameter sniffing = trivial check, so it's faster.

你也可以试试在 SQL Server 2008 中优化未知

OR 运算符

一些想法..

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

If the columns is not nullable, in most cases the optimiser ignores the condition

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

也可以使用IF语句

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

或以类似的方式 UNION ALL.

Or UNION ALL in a similar fashion.

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

Personally, I'd use parameter masking (always) and ISNULL in most cases (I'd try it first)

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

SQL Server 查询优化:其中(Col=@Col 或 @Col=Null)

相关推荐