Efficiency and T / SQL Execution Order

Question

Regarding the execution order of statements in SQL, is there a difference between the following performance quality?

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, maybe there is a link, etc., what can you have, where can I find out more about it?

Thank you, ton

Kyle

Answer 1

The optimizer, since it uses a cost-based optimizer, will determine which “route” is better: it will cost options based on statistics, and then from there. The order of the terms should not have any meaning (although the way you insert things may matter).

EDIT: Oracle used to have - until recently - a rule-based optimizer (RBO), but has now been canceled , since RBO did not work with statistics, you could see differences in query plans depending on the order of predicates.

Answer 2

There will be no difference. You can execute the query in SQL Server and click on the "Display the expected execution plan" icon and check the execution plan for the two queries. They must be identical.

Answer 3

The optimizer is free to reorder and execute predicates, as it finds the most efficient / economical for data retrieval.

The question is, does the order of the criteria have a WHERE clause?

Answer 4

I will add a link.

The query plan is selected based on the statistics in the table and the indices used (taking into account the operations that need to be performed). Regardless of whether the selected query execution plan depends on it, whether it depends on many factors, but the answer to your question is that MS SQL will produce the same plan independently (it will consider the best order for all three conditions and find same result at the end).

However, it should be noted that the planners are not perfect and that they only evaluate the cost, so in some cases (if you know how limited your query planner settings are), you can rewrite the conditions of your request to help the planner see a better way.

This (if possible) should be done only for queries that, as it turns out, are crucial, and also note that such an optimization can slow down when changing data statistics.

In addition, 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 the RDBMS was not to indicate how to extract the data (the declarative nature of the queries) - and in most cases today, query planners will find a good plan for you.

Answer 5

There is no difference in all major sql databases.

Answer 6

No difference. Both will have the same execution plan.

Answer 7

There is a difference in SQL Server 2000. I had a lot of headaches with this. Apparently, he evaluates where the clauses are in the order in which they are defined. To indicate whether I need to filter data by index, I had to add the WITH clause (INDEX (MY_INDEX_NAME1, MY_INDEX_NAME2)) after the FROM statement.