SQL Server Query Optimization: Where (Col=@Col or @Col=Null)

Question

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)

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

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?

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.

Notes

1. SomeTable.ID is indexed
2. It may be related to (or may, in fact, be) a parameter sniffing issue http://stackoverflow.com/questions/211355/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.

Answer 1

You have a combination of issues, most likely

1. Parameter sniffing
2. OR is not a good operator to use

But without seeing the plans, these are educated guesses.

Parameter sniffing

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

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

You could also try OPTIMISE FOR UNKNOWN in SQL Server 2008

The OR operator

Some ideas..

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

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

Also, you can use IF statement

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

Or UNION ALL in a similar fashion.

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
...