May 6, 2003 at 1:01 pm
Is there a performance difference if you rearrange the criteria of a WHERE in a SELECT statement (ie. putting the criteria with less distinct values before criteria with more-distinct values)
For example, in the Northwind database, if the Orders table had millions of records with order dates ranging from years 1901 to 2078 but only contain orders for two employees, is either of the below SQL statement more efficient than the other?
use northwind
select count(*) from orders where employeeid = 1 and orderdate = '1/1/2000';
select count(*) from orders where orderdate = '1/1/2000' and employeeid = 1;
Thanks in advance,
Billy
May 6, 2003 at 2:18 pm
It some rare cases it may be possible but whne the compiler parses the query it will weight the statistics for each idex related to items in the query and generally produce the exact same query plan no matter which way you write.
May 6, 2003 at 3:07 pm
It is better to put the most distinct restriction (date in this case) first anyway. You never know what surprises to expect from the optimizer. So why gamble? It become even more important when your queries become more complex, especially with joins. Look at the order here:
SELECT ...
FROM t1
JOIN t2
ON t1.c1 = @abc
AND t1.c2 = t2.c3
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply