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.
| Author |
Topic |
|
chadmat
The Chadinator
1974 Posts |
 Posted - 2005-02-11 : 17:47:14
|
| I have always thought that the order things appear in the where clause makes no difference as to the performence of a query. The optimizer is smart enough to know in what order to filter to make the query the fastest.However I recently received some advice to rearrange the where clause in a query. I'm not sure if it made any difference or not, but has anyone ever heard of anything like this?Example:WHERE ID = @IDAND abc IN(1, 2, 3)AND xyz != 3As apposed to:WHERE abc IN(1, 2, 3)AND xyz != 3 AND ID = @IDThanks-Chadhttp://www.clrsoft.comSoftware built for the Common Language Runtime. |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-02-11 : 17:58:38
|
| Try it and look at the query plan.Pretty sure on something that simple it won't make a difference.What was the justification for the change?==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2005-02-11 : 18:12:58
|
| I have see that the order does make a difference sometimes.Almost all of the time, it doesn't matter, but sometime the optimiser doesn't figure it out properly. Because of that, I always put them in order that they are in the index that I want to use.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-02-11 : 19:16:50
|
| Have you (hopefully simple) examples of that?If the cost is the same then the optimiser might choose a different plan depending on what it tests first. If the query is complex then the optimiser might give up before testing all the possibilities but apart from that you may have come across a bug. ==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2005-02-14 : 11:16:11
|
| The advice came from MS. They were looking at a poor performing view. There suggestion was to rewrite it as a Stored Proc, and rearrange some of the predicates. I had already rewritten it as a UDF, and it was much faster, so I'm not sure if the predicate rearanging had any affect as it was already fast by the time I did that.I haven't bothered to look at the Query plans. I was just curious if my original assumption was right, that the Optimizer OUGHT to know what to do regardless of the order.-Chadhttp://www.clrsoft.comSoftware built for the Common Language Runtime. |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-02-14 : 11:45:04
|
| Very general statement coming: Yes the optimizer should know how to best layout the plan. However, if YOU also know in what order your tables should be listed as well as what order your criteria should be listed to increase selectivity early on in the plan, it's good form to place them in that order. (just in case the optimizer is having "one of those" days)Here's a really bad analogy:When I hand over a wad of cash to a store clerk when paying for something, I always have them arranged in order. I know they can arrange and count them as they put them in the register but it goes quicker if I do it for them while I'm waiting in line.Be One with the OptimizerTG |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-02-14 : 12:02:17
|
that's interesting... i do the same thing, TG... it's all about being one with the optimizer, huh?  Go with the flow & have fun! Else fight the flow  |
|
|
|
|
|
|
|
|
Predicate Order