T/SQL Efficiency and Order of Execution

Question

Hi All,

In regards to the order of execution of statements in SQL, is there any difference between the following performance wise?

SELECT * FROM Persons
WHERE UserType = 'Manager' AND LastName IN ('Hansen','Pettersen')

And:

SELECT * FROM Persons
WHERE LastName IN ('Hansen','Pettersen') AND UserType = 'Manager'

If there is any difference, is there perhaps a link etc. that you may have where one can learn more about this?

Thanks a ton,

Kyle

Answer 1

In all the major sql databases, there is no difference.

Answer 2

The optimizer, since it uses a cost based optimizer, will decide which "route" is best: it will cost the options based on statistics and then go from there. The order of terms should make no difference (although how you nest things, might make a difference).

EDIT: Oracle used to have - until fairly recently - a rule-based optimizer (RBO), but that has now been phased out. As the RBO didn't work off statistics, it was possible to see differences in query plans depending on the order of predicates.

Answer 3

No difference at all. Both will result in the same execution plan.

Answer 4

There will be no difference. You can pull the query up in SQL Server and click on the Display Estimated Execution Plan icon and check the execution plan for the two queries. They should be identical.

Answer 5

The optimizer is free to rearrange and execute predicates as it finds most efficient/cost effective to retrieve the data.

Does the order of criteria the WHERE clause matter?

Answer 6

I'll add the following link.

Query plan is selected based on statistics on the table and indexes involved (given the operations that need to be performed). Whether chosen query execution plan is the best depends on lot of factors - but the answer for your question is that MS SQL will produce the same plan regardless (it will consider the best order for all three conditions and will find the same result in the end).

However, it should be noted that planners are not perfect and that they only estimate the cost, so in some cases (providing you know how your query planner's limitations in details) you might be able to rewrite your query conditions to help the planner see a better path.

This (if even possible) should be attempted only for queries that are proven to be crucial and also note that this kind of optimization might slow things down when data statistics change.

Also, in most cases there are better ways (changing indexes) to optimize queries, and this should be left to the query planner.

One of the main points of RDBMS was not to specify how to retrieve data (declarative nature of queries) - and in most cases today's query planners will find a good plan for you.

Answer 7

In SQL Server 2000 there is a difference. I had lots of headaches with this one. Apparently it evaluates where clauses in the order they are defined. To specify whether I wanted the data filtered through an index, I had to add the WITH(INDEX(MY_INDEX_NAME1,MY_INDEX_NAME2)) clause after FROM statement.