SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 does order of tests matter?
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

jezemine
Flowing Fount of Yak Knowledge

USA
2886 Posts

Posted - 05/03/2007 :  02:25:14  Show Profile  Visit jezemine's Homepage  Reply with Quote
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

Slovenia
11751 Posts

Posted - 05/03/2007 :  05:24:30  Show Profile  Visit spirit1's Homepage  Reply with Quote
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
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 05/03/2007 :  05:25:49  Show Profile  Visit spirit1's Homepage  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30277 Posts

Posted - 05/03/2007 :  05:41:11  Show Profile  Visit SwePeso's Homepage  Reply with Quote
select	1
from	mtyable
where	moreSelective = 12
	and notVerySelective = 1

union all

select	0
from	mtyable
where	moreSelective <> 12
	or notVerySelective <> 1


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 05/03/2007 :  06:21:49  Show Profile  Reply with Quote
That's TWO table scans, isn't it Peso?
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 05/03/2007 :  06:23:08  Show Profile  Reply with Quote
"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
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 05/03/2007 :  06:29:39  Show Profile  Visit spirit1's Homepage  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 05/03/2007 :  06:35:15  Show Profile  Reply with Quote
Even for a CASE in a SELECT ? (I can see the issue with something in the WHERE)

Kristen

Edited by - Kristen on 05/03/2007 06:35:31
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 05/03/2007 :  06:40:55  Show Profile  Visit spirit1's Homepage  Reply with Quote
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
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2886 Posts

Posted - 05/03/2007 :  11:04:56  Show Profile  Visit jezemine's Homepage  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 05/03/2007 :  17:08:58  Show Profile  Reply with Quote
"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
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 05/03/2007 :  18:18:21  Show Profile  Visit spirit1's Homepage  Reply with Quote
well you had to pay for something, right?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 05/03/2007 :  20:12:05  Show Profile  Reply with Quote
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.
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 05/04/2007 :  01:44:50  Show Profile  Reply with Quote
"you cannot tell which way SQL will evaluate your clauses"

For example?
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2886 Posts

Posted - 05/04/2007 :  02:27:25  Show Profile  Visit jezemine's Homepage  Reply with Quote
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
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 05/04/2007 :  06:32:33  Show Profile  Visit spirit1's Homepage  Reply with Quote
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
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2886 Posts

Posted - 05/04/2007 :  09:56:03  Show Profile  Visit jezemine's Homepage  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 05/04/2007 :  10:54:52  Show Profile  Reply with Quote
OTOH you could use that as a mechanism for flushing out errors on the "wrong side" of your && expressions, and reduce the testing effort!!
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 05/04/2007 :  10:58:08  Show Profile  Visit spirit1's Homepage  Reply with Quote
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

Edited by - spirit1 on 05/04/2007 10:59:10
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 05/04/2007 :  12:40:12  Show Profile  Reply with Quote
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: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=82443

Edited by - Lamprey on 05/04/2007 12:41:11
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2886 Posts

Posted - 05/04/2007 :  12:42:09  Show Profile  Visit jezemine's Homepage  Reply with Quote
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

Edited by - jezemine on 05/04/2007 12:43:21
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000