-
Expert Member
Where Condition
In a query I am using three coditions like this
Select * from xxxx
Where condition1
and condition2
and condition3
Does it has any effect on performance if I change the order of coditions as follows
Select * from xxxx
where condition2
and condition1
and condtion3
-
Expert Member
Re: Where Condition
All database optimizers are smart enough to figure out which columns have indexes and to adjust the execution plan accordingly, no matter which sequence the conditions are presented in.
-
Re: Where Condition
That does not matter at all. But it will count if involvs joins.
-
Contributing Member
Re: Where Condition
Dear Friend,
Changing the order will definitely affect the execution speed.
But only in case of Multiple joins. In case of simple conditions,
no need to change the sequence,but for more than one join
condition, you have to use proper sequence for faster execution.
In case of more tables, always specify master table joins first,then
specify detail table joins and the specify static value checks if any.
For e.g.
Suppose there are:
3 Master Tables A,B,C
3 Detail Tables D,E,F
3 Fixed Conditions
select columns
from A,B,C,D,E,F
WHERE A.column=B.column
AND B.column=C.column
AND C.column=D.column
AND D.cloumn=E.column
AND E.column=F.column
AND D.column=xyz (Fixed Condition)
AND E.column=abc (Fixed Condition)
................................. and so on
Query execution starts from the bottom i.e. from last condition
to first condition.
So always write fixed conditions at the bottom if any
then detail table joins
and then at the top, master table joins
This will definitely result in better execution
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules