Position of conditions in WHERE clause

Question

Post reply

Position of conditions in WHERE clause

govind-211283

SSC Eights!

Points: 962
More actions
April 11, 2005 at 1:20 am

#165071

Hi, I want to know is their have any advantange to arrange the order of conditions in the WHERE clauses? Does it makes any difference in query performance. Like if I have around 1 million records in a table (Tran PK, CustomerID, TranDate)
now if I execute the query like :
SELECT * FROM Tbl WHERE CustomerID = 10 AND Tran = 1000
OR should i shuffle the order of my conditions like :
SELECT * FROM Tbl WHERE Tran = 1000 AND CustomerID = 10
Does the query parser first evalulate the expression from Right To Left or Left to Right.
My real question is that do we place the main condition at the most Right hand side or we can place it anywhere in preceeding?

Govind.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Dinesh Asanka SSChampion Points: 11058 More actions · Answer 1

You can simply check this by enabling Execution plan (Ctrl +K) in the query analyser.

My Blog: http://dineshasanka.spaces.live.com/

DCPeterson SSCoach Points: 15266 More actions · Answer 2

Generally the SQL Server optimizer does a very good job of "rewriting" queries so you don't have to worry about it. About the only time I've seen problems is when the statistics are out of date or non-existent. But once again, by default, SQL Server does an admirable job of keeping those up to date.

/*****************

If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek

*****************/

Vladan SSC-Insane Points: 22072 More actions · Answer 3

No, it doesn't change anything if you switch the conditions in WHERE clause. About the only thing where order of conditions can play any role is in CASE statements. Conditions of a CASE statement are evaluated in the order in which you write them; otherwise, optimizer decides about the order of evaluation.

Vladan

Mike Metcalf SSCrazy Eights Points: 8763 More actions · Answer 4

If you find that the optimiser is making a hash of things (missing an obvious step that will significantly reduce the workload) and the queries are taking too long to complete then you can try experimenting with SET FORCEPLAN ON (check BOL for more details)

I wouldn't recomment using this hint but if it helps during your testing then it's up to you to decide whether to or not.

lholman SSCrazy Points: 2810 More actions · Answer 5

Govind,

I agree with everything said above but one simple thing to always remember in SELECT clauses is only to select exactly what you want to return, i.e instead of the following:

SELECT * FROM tbl

try:

SELECT column1, column2 FROM tbl

This is often overlooked even by more lazy developers and DBA's.

Lloyd

govind-211283 SSC Eights! Points: 962 More actions · Answer 6

Thanks all for replying.

I got my answer which is "SQL query analyzer do this judgement accrodingly" that's what i was looking for.

Thanks again

Bye