Odd Construct in a WHERE clause

Question

Odd Construct in a WHERE clause

Viewing 5 posts - 31 through 34 (of 34 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Richard Fryar (9/10/2008)
I've just tried this on SQL Server 2005 Standard
select 'x' where 1=0 and 1/0=0
select 'x' where 1/0=0 and 1=0
select 'x' where 1=1 and 1/0=0
select 'x' where 1/0=0 and 1=1
The first two return empty result sets, the second two return "divide by zero" errors.
This proves that SQL Server does do short circuit evaluation, but the order is not important. The optimizer is cleverer than that.

On SQL Server 2k, the order is important:

select 'x' where sqrt(3)=3 and 1/0=0

select 'x' where 1/0=0 and sqrt(3)=3

The first query returns an empty result set, the second fails with "divide by zero" error 😉

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Glen SSCertifiable Points: 6737 More actions · Answer 2

Original article written by Marc Cohen is great, but not always true.

http://beingmarkcohen.com/?p=62

Read a great response by sc0rp10n in the same post.

Ultimate and short article about short-circuiting and SQL server is here:

http://weblogs.sqlteam.com/jeffs/archive/2008/02/22/sql-server-short-circuit.aspx

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 3

Getting back to the question of why the query performs differently in a procedure, an execution plan must be chosen when the procedure is compiled with no knowledge of the run-time parameter value. It's not "confused" by "WHERE col1 = @param1 OR @param1 = 0", it just has to decide on one plan based on limited information. It will probably assume a non-zero parameter value and choose to do an index scan, possibly with a bookmark lookup. When a zero argument is passed to the procedure a table scan (or a different index) would probably be much more efficient.

When the query is used in a batch, the parameter values are known when the statement is compiled and the most efficient execution plan may be chosen.

The "WHERE column = COALESCE(@param, column)" version (with a NULL value for @param instead of zero) often works better because the use of a function will probably make the query optimizer overlook this clause when choosing indexes. It doesn't give you the best plan in all cases, but it should avoid the worst-case situations in the original post.

With a small number of optional parameters, you can create a procedure with every variation of the query using "WHERE col1 = @param1", "WHERE col2 = @param2", "WHERE col1 = @param1 AND col2 = @param2", etc. and use "IF @param1 = 0", "IF @param2 = 0" statements to execute the correct one. This quickly leads to a maintenance nightmare if more parameters are added, but it will guarantee that the optimum plan will be used for any combination of arguments.

Dynamic SQL is often the best solution, as it can handle more parameters and the cost of compiling the statement is less than the cost of waiting forever for a bad execution plan to execute.

jomills SSC Journeyman Points: 75 More actions · Answer 4

Thanks Scott,

I had to come up with a temporary work around while I worked on the problem of this Stored proc, I will incorporate some of your suggestions in the orig proc and see how it turns out.

-Joseph

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 5

Since you're using 2005, a relatively low cost fix (instead of dynamic SQL) might simply be to force that select statement to recompile each and every single time. Meaning - add OPTION (RECOMPILE) to the select statement. this will force a statement level recompile (which tends to be really fast), giving you the best exec plan during each run.

Interestingly enough - if you look up the Query Hints entry in BOL, the example shown is something that looks to be almost a clone of your example with the OPTION (RECOMPILE) hint.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?