T-SQL - Non optimal plan is used - WHERE clause should be shortcircuited

Question

We have many "search stored procedures" that take multiple nullable parameters for searching rows of data in different tables. They're usually built like this:

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)

This can go on for up to 30-40 parameters and what we've noticed is even if only parameter1 is provided, the execution plan goes through index scans of the other tables which can slow down the query significantly (few seconds). Tests show us that keeping only the first line from the WHERE statement makes the query instant.

1. I've read that shortcuiting is not possible, but are there work around or ways to construct queries that would possibly be more efficient?

2. We currently work around this problem by having different versions of the same SELECT/FROM/JOINS but with different set of parameters in the WHERE clause and depending on which parameters are passed we choose the proper select statement to go through. This is long, messy and hard to maintain.

Answer 1

The query plans in SQL Server is compiled and stored for reuse. Even if SQL Server sees that your parameters are null it has to come up with a query plan that will work for the cases where they are not null.

Query hint option (recompile) was introduced in SQL Server 2005 but it was not until SQL Server 2008 that it actually had an effect on the kind of query you have here.

When the query is recompiled every time it will not be stored in the query plan cache so SQL Server is free to optimize out the checks against parameters that is null.

Read more about it here Dynamic Search Conditions in T-SQL

Some sample code you can test on to see the difference in query plans. The first call to the SP will do a index seek and the second will do a clustered index scan.

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