| 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 1980blog: 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 1980blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-05-03 : 05:41:11
|
| [code]select 1from mtyablewhere moreSelective = 12 and notVerySelective = 1union allselect 0from mtyablewhere moreSelective <> 12 or notVerySelective <> 1[/code]Peter LarssonHelsingborg, 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 usefullfor the rest of the query.if the left-to right was appliable then it would work._______________________________________________Causing trouble since 1980blog: 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 1980blog: 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 1980blog: 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 1980blog: 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 1980blog: 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'tso if you really must evaluate all conditions in an if in c# use | and &._______________________________________________Causing trouble since 1980blog: 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 |
|
|
|
Next Page
|
does order of tests matter?