does order of tests matter? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

does order of tests matter?

Author

Topic

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-05-03 : 02:25:14

This is to settle a bet with a colleague.

Let's say I have a heap, with two int columns. One is much more selective than the other, but neither is unique. Which of these queries, if any, is more efficient? Both are checking for particular values in the two coluns, just the order of the check is reversed.

this one tests the non-selective column first, then the selective column.


select 
	case 
	when notVerySelective=1 and moreSelective=12 then 1
	else 0
	end 
from MyTable

this one tests in the other order.


select 
	case 
	when moreSelective=12 and notVerySelective=1 then 1
	else 0
	end 
from MyTable

my colleague says this one is better because, he maintains, the when uses short-circuit logic, and since the first check will rarely be satisfied since the column is highly selective, it won't have to do the second check very often, thus saving some cycles.

But somehow I feel like common sense may be wrong here. both queries will force table scans, and who really knows what the optimizer does with the two checks? Somehow I feel like the optimizer will do the right thing either way. Maybe my faith in the optimizer is too great?

Also I feel like my colleague is thinking procedurally. I would certainly agree with him if the check were an if clause in a C++ program - there it's always best to put the check that's least likely to be true first, to avoid further checks in the clause (short circuit). but in SQL it's not so clear, to me anyway.

www.elsasoft.org

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-03 : 05:24:30

both conditions are executed in both queries.
since it's a heap it will always use a table scan.

based on the statistics on the table the plan might differ but it should be the same for both queries.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-03 : 05:25:49

plus if i remember correctly the short circuiting comes right after parsing and before plan generation

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-03 : 05:41:11

[code]select 1
from mtyable
where moreSelective = 12
and notVerySelective = 1

union all

select 0
from mtyable
where moreSelective <> 12
or notVerySelective <> 1[/code]

Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2007-05-03 : 06:21:49

That's TWO table scans, isn't it Peso?

Kristen
Test

22859 Posts

Posted - 2007-05-03 : 06:23:08

"both conditions are executed in both queries."

I dunno why SQL Server does that. I would have thought left-to-right and give up when outcome is guaranteed would be a better [i.e. less effort] approach ...

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-03 : 06:29:39

this acctually isn't that that feasible in sql server.
since the plan is chosen amongst many generated, it could happen that evaluating the non selective one could prove more usefull
for the rest of the query.

if the left-to right was appliable then it would work.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Kristen
Test

22859 Posts

Posted - 2007-05-03 : 06:35:15

Even for a CASE in a SELECT ? (I can see the issue with something in the WHERE)

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-03 : 06:40:55

i'm pretty sure that yes.
because what if you have an exists or some other condition that had a select or something.
it simply has to evaluate every expression in the statement so it can get the correct threashold for the plan generation.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-05-03 : 11:04:56

quote:
Originally posted by spirit1

both conditions are executed in both queries.
since it's a heap it will always use a table scan.

based on the statistics on the table the plan might differ but it should be the same for both queries.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

yay! I was right. I'll rub his nose in it today.

www.elsasoft.org

Kristen
Test

22859 Posts

Posted - 2007-05-03 : 17:08:58

"what if you have an exists or some other condition that had a select or something"

Yup, good point. Its rather nice to think its going to all that trouble to straighten out my Crap Code!

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-03 : 18:18:21

well you had to pay for something, right?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-05-03 : 20:12:05

Not to add any more fuel, but it depends on how you define efficiency. Does SQL short circuit? Yes, will it help in this scenario? Probably not, because, as mentioned, the query will table scan. But, you might save some nominal amount of CPU if is short circuits. Which leads me to my next point is that you cannot tell which way SQL will evaluate your clauses.

Kristen
Test

22859 Posts

Posted - 2007-05-04 : 01:44:50

"you cannot tell which way SQL will evaluate your clauses"

For example?

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-05-04 : 02:27:25

this I do know to be true: in a CASE, the first WHEN that's satisfied is the one that's used, even if subsequent WHEN clauses are also satisfied. it says so in BOL. it would be a disaster otherwise imo.

but as for the tests in a single WHEN, BOL is silent on the order of evaluation as far as I can tell. hence my post.

www.elsasoft.org

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-04 : 06:32:33

that's logical.
when is just a fancy if expression while tests in a when are conditions.
short circuiting is a matter of how is the programming language defined.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-05-04 : 09:56:03

yes.

actually, this reminds me: even a language like C++ doesn't short circuit if you overload the && operator in one of your classes. you should not do that! if you do, you can't rely on short circuit logic anymore when that class appears in an expression with &&, because now both sides must be evaluated. why? because the && turns into a function call with left and right args as parameters!

www.elsasoft.org

Kristen
Test

22859 Posts

Posted - 2007-05-04 : 10:54:52

OTOH you could use that as a mechanism for flushing out errors on the "wrong side" of your && expressions, and reduce the testing effort!!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-04 : 10:58:08

exactly.

a note about c#
&& and || is shortcircuited
& and | isn't

so if you really must evaluate all conditions in
an if in c# use | and &.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-05-04 : 12:40:12

quote:
Originally posted by Kristen

"you cannot tell which way SQL will evaluate your clauses"

For example?

I was not entirely sure about this myself until a few weeks ago because it has not really been an issue. But, this thread talk about a potential headache of trying to force the order of evaluation because of bad DB design: [url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=82443[/url]

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-05-04 : 12:42:09

spirit: I am confused by your comment. & and && are completely different.

one is logical, the other is bitwise. it makes no sense to do bitwise operations on a bool in C# - it wouldn't even compile.

I think I must be missing your point - you can't interchange & and && willy-nilly.

www.elsasoft.org

Previous Page&nsp; Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources