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 |
|
obiwaugh
Starting Member
27 Posts |
 Posted - 2004-12-24 : 02:48:12
|
| Does the order of items in the WHERE clause have any effect on performance?For example:WHERE DEPTID = 2100 AND EFFDT = some complex subquery that joins a couple of tablesMy thought is that, if the items are processed in order, then the first item would reduce the amount of work done by the 2nd item. Is that the case, or am I making this up as I go along? :-)I know enough to know that I don't know enough. |
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2004-12-24 : 03:56:04
|
| As I understand it - and someone might know far more about it than me and prove me wrong. Things are processed in order (sort of) so if you have WHERE DEPTID = 2100AND EFFDT = some complex subquery that joins a couple of tablesthen DEPTID = 2100 will be processed first and only if this succeeds will EFFDT = some complex subquery that joins a couple of tablesbe processed.Obviously that only applies to an AND and couldn't work with an ORThis is one way to optimise your queries a bit as WHERE EFFDT = some complex subquery that joins a couple of tablesAND DEPTID = 2100Is likely to be less efficient in most cases because the complex subquery would be processed first unless the system is smart enough to work this outSteveAnd how is education supposed to make me feel smarter? Besides, every time I learn something new, it pushes some old stuff out of my brain. |
 |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-12-24 : 04:09:08
|
well the sql parser takes the where and "twists" it to its needs. so it doesn't necessarily means it will be processed in the way it was written.Go with the flow & have fun! Else fight the flow  |
|
|
|
|
|
|
WHERE clause and performance...