Correct way to handle 'where' options in SQL?

Question

Say you have a stored procedure, and it takes an optional parameter. You want to use this optional parameter in an SQL query. This is usually how I saw it:

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

It seems to work well, however it does cause a lot of logical readings if you run the query using STATISTICS IO ON. I also tried the following option:

 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 gives the same amount of high readings. If we convert SQL to a string, then call sp_ExecuteSQL on it, the read numbers are almost zero:

 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 

I've gone crazy? Why is it optional when clauses are so difficult to obtain?

Update: I basically ask if there is a way to keep the standard syntax inside the stored procedure and get low logical reads, like the sp_ExecuteSql method does. It seems completely crazy to me to create a chain ... not to mention the fact that it is more difficult to maintain, debug, visualize ..

Answer 1

If we convert SQL to a string, then call sp_ExecuteSQL on it, the reading will be almost zero ...

• Because your request no longer evaluates OR, which, as you see, kills sargability
• The query plan is cached using sp_executesql; SQL Server does not need to perform hard analysis ...

Great Resource: The Curse and Blessing of Dynamic SQL

While you are using parameterized queries, you should avoid SQL Injection attacks .

Answer 2

This is another variation of the optional parameters method:

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

I am sure he will have the same performance issue. If performance is # 1, then you are likely to stick with forking logic and close duplicate queries or string strings, which is equally painful in TSQL.

Answer 3

You use the OR clause (implicitly and explicitly) for the first two SQL statements. The last criterion is "And." “OR” is always more expensive than “AND” criteria. No, you're not crazy, you should expect.

Answer 4

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

Change the use of the “or” syntax to an allied approach, you will see 2 calls that should support as little logical reading 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 remove duplicate results, use "union" instead of "union all".

EDIT: Demonstrating that the optimizer is smart enough to rule out checking 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 the use of the "or" syntax on the two approaches to the search, you will see 2 different plans that should support as little logical reading 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 with your programmer to reduce duplication here. Understand, you ask for two fundamentally different execution plans and require two requests to create two plans.