Proper way to handle 'optional' where clause filters in SQL?

Question

Let's say you have a stored procedure, and it takes an optional parameter. You want to use this optional parameter in the SQL query. Typically this is how I've seen it done:

SELECT * FROM dbo.MyTableName t1
WHERE t1.ThisField = 'test'
AND (@MyOptionalParam IS NULL OR t1.MyField = @MyOptionalParam)

This seems to work well, however it causes a high amount of logical reads if you run the query with STATISTICS IO ON. I've also tried the following variant:

SELECT * FROM dbo.MyTableName t1
WHERE t1.ThisField = 'test'
AND t1.MyField = CASE WHEN @MyOptionalParam IS NULL THEN t1.MyField ELSE @MyOptionalParam END

And it yields the same number of high reads. If we convert the SQL to a string, then call sp_ExecuteSQL on it, the reads are almost nil:

DECLARE @sql nvarchar(max)

SELECT @sql = 'SELECT * FROM dbo.MyTableName t1
WHERE t1.ThisField = ''test'''

IF @MyOptionalParam IS NOT NULL
BEGIN
     SELECT @sql = @sql + ' AND t1.MyField = @MyOptionalParam '
END

EXECUTE sp_ExecuteSQL @sql, N'@MyOptionalParam', @MyOptionalParam

Am I crazy? Why are optional where clauses so hard to get right?

Update: I'm basically asking if there's a way to keep the standard syntax inside of a stored procedure and get low logical reads, like the sp_ExecuteSql method does. It seems completely crazy to me to build up a string... not to mention it makes it harder to maintain, debug, visualize..

Answer 1

You're using "OR" clause (implicitly and explicitly) on the first two SQL statements. Last one is an "AND" criteria. "OR" is always more expensive than "AND" criteria. No you're not crazy, should be expected.

Answer 2

This is another variation on the optional parameter technique:

SELECT * FROM dbo.MyTableName t1
WHERE t1.ThisField = 'test'
AND t1.MyField = COALESCE(@MyOptionalParam, t1.MyField)

I'm pretty sure it will have the same performance problem though. If performance is #1 then you'll probably be stuck with forking logic and near duplicate queries or building strings which is equally painful in TSQL.

Answer 3

If we convert the SQL to a string, then call sp_ExecuteSQL on it, the reads are almost nil...

1. Because your query is no longer evaluating an OR, which as you can see kills sargability
2. The query plan is cached when using sp_executesql; SQL Server doesn't have to do a hard parse...

Excellent resource: The Curse & Blessing of Dynamic SQL

As long as you are using parameterized queries, you should safe from SQL Injection attacks.

Answer 4

EDIT: Adding link to similar question/answer with context as to why the union / if...else approach works better than OR logic (FYI, Remus, the answerer in this link, used to work on the SQL Server team developing service broker and other technologies)

Change from using the "or" syntax to a union approach, you'll see 2 seeks that should keep your logical read count as low as possible:

SELECT * FROM dbo.MyTableName t1
WHERE t1.ThisField = 'test'
AND @MyOptionalParam IS NULL 
union all
SELECT * FROM dbo.MyTableName t1
WHERE t1.ThisField = 'test'
AND t1.MyField = @MyOptionalParam

If you want to de-duplicate the results, use a "union" instead of "union all".

EDIT: Demo showing that the optimizer is smart enough to rule out scan with a null variable value in UNION:

if object_id('tempdb..#data') > 0
    drop table #data
go

-- Put in some data
select  top 1000000
     cast(a.name as varchar(100)) as thisField, cast(newid() as varchar(50)) as myField
into    #data
from    sys.columns a
cross join sys.columns b
cross join sys.columns c;
go

-- Shwo count
select count(*) from #data;
go

-- Index on thisField
create clustered index ixc__blah__temp on #data (thisField);
go

set statistics io on;
go

-- Query with a null parameter value
declare @MyOptionalParam varchar(50);
select  *
from    #data d 
where   d.thisField = 'test'
and  @MyOptionalParam is null;
go

-- Union query
declare @MyOptionalParam varchar(50);
select  *
from    #data d 
where   d.thisField = 'test'
and  @MyOptionalParam is null
union all
select  *
from    #data d 
where   d.thisField = 'test'
and  d.myField = '5D25E9F8-EA23-47EE-A954-9D290908EE3E';
go

-- Union query with value
declare @MyOptionalParam varchar(50);
select @MyOptionalParam = '5D25E9F8-EA23-47EE-A954-9D290908EE3E'
select  *
from    #data d 
where   d.thisField = 'test'
and  @MyOptionalParam is null
union all
select  *
from    #data d 
where   d.thisField = 'test'
and  d.myField = '5D25E9F8-EA23-47EE-A954-9D290908EE3E';
go

if object_id('tempdb..#data') > 0
    drop table #data
go

Answer 5

Change from using the "or" syntax to a two query approach, you'll see 2 different plans that should keep your logical read count as low as possible:

IF @MyOptionalParam is null
BEGIN

  SELECT *
  FROM dbo.MyTableName t1

END
ELSE
BEGIN

  SELECT *
  FROM dbo.MyTableName t1
  WHERE t1.MyField = @MyOptionalParam

END

You need to fight your programmer's urge to reduce duplication here. Realize you are asking for two fundamentally different execution plans and require two queries to produce two plans.