| Author |
Topic |
|
vladimir_grigoro
Yak Posting Veteran
62 Posts |
 Posted - 2002-07-26 : 09:44:21
|
| Hi All,I just wonder if there any importance how is the order of conditions in WHERE clause on SELECT or UPDATE statement. I mean:...WHERE CONDITION1 AND CONDITION2 AND CONDITION3 ect.Is there any sense, which conditions sill be first depending on the index or something else. I am using SQL Server 2000.Thanks in advcance.The Rebel |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-07-26 : 09:51:26
|
| No. Ordering the where clause should not effect the execution plan.However, putting some conditionals in the ON of part of your JOIN in the FROM clause, rather than in the WHERE, will sometimes make a difference.<O> |
 |
|
|
vladimir_grigoro
Yak Posting Veteran
62 Posts |
Posted - 2002-07-26 : 09:54:01
|
What will be the difference? Faster execution or what?quote:
No. Ordering the where clause should not effect the execution plan.However, putting some conditionals in the ON of part of your JOIN in the FROM clause, rather than in the WHERE, will sometimes make a difference.<O>
The Rebel |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-07-26 : 09:55:08
|
| Not really, they get processed left-to-right, with expressions in parentheses evaulated first. SQL Server DOES has a shortcut for processing logical arguments though. In your example, if CONDITION1 was false, then SQL Server would avoid evaluating the remaining conditions. Since they are all AND'ed together, if the first one is false the entire thing will be false right away. Conversely with a bunch of expression OR'd together; if the 1st one evaluates as true, the whole shebang is true, and it just stops at that point.Depending on the nature of your data, and the types of conditions you evaluate, you might get some improvement by moving them around to take advantage of this feature, but you'd need to test it. |
|
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2002-07-26 : 11:07:00
|
| * Moving conditions between WHERE and ON in outer joins may change the meaning of the query. End of story.* Moving conditions between WHERE and ON in inner joins does not change the meaning of the query.* Does moving conditions between WHERE and ON in inner joins influence the query plan? I don't see any reason why it would, but Rob insists it does.* Does altering the order of conditions make a difference? I think it can only make a difference for pairs of conditions where the optimizer assigns them the same cost and selectivity estimates. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-07-26 : 11:38:00
|
quote:
I don't see any reason why it would, but Rob insists it does.
Because I've had elaborate queries with elaborate WHERE clauses optimize better when I move the JOIN conditions into a JOIN clause. The logic behind the two has been separated since SQL 6.5. It may be that your queries optimize the same using either syntax, but mine don't. |
|
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2002-07-27 : 02:37:48
|
Maybe I'm putting too much faith in Kalen, but this is what Inside MS SQL Server 2000 says:quote:
The ANSI JOIN syntax specifies the JOIN conditions in the ON clauses (one for each pair of tables), and the search conditions are specified in the WHERE clause—for example, WHERE state <> 'CA'. Although slightly more verbose, the explicit JOIN syntax is more readable. There's no difference in performance; behind the scenes, the operations are the same.
If you have actual counterexamples, then I'd be very interested to see them: as it is, I can't see what rationale could be used here to drive the optimizer in different directions. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-07-27 : 07:23:41
|
| Unfortunately I don't work at the company anymore and I don't have access to the database. It also was running SQL 7.0, maybe that was what caused the different optimizations, and now 2000 has it sorted out. I don't have that kind of environment anymore and I'm not sure I can duplicate it. Basically the WHERE syntax would ignore indexes that the JOIN syntax would always grab. I should mention that I *usually* got the same plan with either method, but there were a lot of times when I didn't. 6.5 also did funky things as well, a lot of those were smoothed out in 7.0 too. |
|
|
|
|
WHERE Clause