T-SQL Where Clause Case Statement Optimization (optional parameters to StoredProc)?

I read somewhere that the second way will short circuit and never eval the second part if true. My DBA said it forces a table scan You read wrong; it will not short circuit. Your DBA is right; it will not play well with the query optimizer and likely force a table scan The first option is about as good as it gets.

Your options to improve things are dynamic sql or a long stored procedure with every possible combination of filter columns so you get independent query plans. You might also try using the "WITH RECOMPILE" option, but I don't think it will help you.

I read somewhere that the second way will short circuit and never eval the second part if true. My DBA said it forces a table scan. You read wrong; it will not short circuit.

Your DBA is right; it will not play well with the query optimizer and likely force a table scan. The first option is about as good as it gets. Your options to improve things are dynamic sql or a long stored procedure with every possible combination of filter columns so you get independent query plans.

You might also try using the "WITH RECOMPILE" option, but I don't think it will help you.

Damn. Beat me to it. +1 :) – DVK Oct 8 '09 at 19:19 1 The first option will not necessarily return the same results as the 2nd one.

If you have rows in your table with NULL values, they will NOT be returned by the "option 1" query. For example.... Select * From Table Where NullableColumn = NullableColumn – G Mastros Oct 8 '09 at 19:26 All three parms are NOT NULL, so that's not an issue in this case. Looks like I might be stuck with option 1.

– IronicMuffin Oct 8 '09 at 19:28 Thank you all for the fast responses and great advice. – IronicMuffin Oct 8 '09 at 19:30.

If you are running SQL Server 2005 or above you can use IFs to make multiple version of the query with the proper WHERE so an index can be used. Each query plan will be placed in the query cache. Also, here is a very comprehensive article on this topic: Dynamic Search Conditions in T-SQL by Erland Sommarskog it covers all the issues and methods of trying to write queries with multiple optional search conditions here is the table of contents: Introduction The Case Study: Searching Orders The Northgale Database Dynamic SQL Introduction Using sp_executesql Using the CLR Using EXEC() When Caching Is Not Really What You Want Static SQL Introduction x = @x OR @x IS NULL Using IF statements Umachandar's Bag of Tricks Using Temp Tables x = @x AND @x IS NOT NULL Handling Complex Conditions Hybrid Solutions – Using both Static and Dynamic SQL Using Views Using Inline Table Functions Conclusion Feedback and Acknowledgements Revision .

1 for linking anything by Mr Sommarskog – Joel Coehoorn Oct 8 '09 at 19:21 Bookmarked...thank you. We use a mix of 2000/2005 servers, unfortunately this one is 2000. – IronicMuffin Oct 8 '09 at 19:24 the article covers numerous ways to handle variable search conditions, the IFs is just one – KM.

Oct 8 '09 at 19:33.

If you pass in a null value when you want everything, then you can write your where clause as Where colName = IsNull(@Paramater, ColName) This is basically same as your first method... it will work as long as the column itself is not nullable... Null values IN the column will mess it up slightly. The only approach to speed it up is to add an index on the column being filtered on in the Where clause. Is there one already?

If not, that will result in a dramatic improvement.

It seems like it's basically the same operation but checking null instead of -1. Is IsNull more efficient than a CASE? – IronicMuffin Oct 8 '09 at 19:20 @IronicMuffin, (I like your moniker) No you're exactly right, this is equivilent to yr first method... – Charles Bretana Oct 8 '09 at 19:52.

No other way I can think of then doing: WHERE (MyCase IS NULL OR MyCase = @MyCaseParameter) AND .... The second one is more simpler and readable to ther developers if you ask me.

It's also slow as molasses. Don't use it. – Joel Coehoorn Oct 8 '09 at 19:17 @Joel do you have anything to back that up?

Not that I doubt you, I'm just looking for some hard data one way or the other. – Jonas Oct 8 '09 at 19:19 I have two indicators that the second method may force a table scan, and that is not a good thing with 1.5 million records. – IronicMuffin Oct 8 '09 at 19:21 @Joel: This notion of slow as molasses is completly false.

Are you stating that dynamically building a WHERE clause inside of your client app is better? I beg to differ...it is clean CORRECT code and his DBA should be prompted for that. – JonH Oct 8 '09 at 19:28.

SQL 2008 and later make some improvements to optimization for things like "(MyCase IS NULL OR MyCase = @MyCaseParameter) AND ...." If you can upgrade, and if you add an OPTION (RECOMPILE) to get decent perf for all possible param combinations (this is a situation where there is no single plan that is good for all possible param combinations), you may find that this performs well. blogs.msdn.com/b/bartd/archive/2009/05/0....

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.