Reputation: 3
Employee Table
select * from Employee where Salary=60000 and FirstName = 'Mark'
select * from Employee where FirstName = 'Mark' and Salary = 60000
In above queries where clause conditions are reversed, I want to know which query runs faster compared to each other.
I. If table does not have any indexes except primary key,Then which query runs faster ? II. Is it depend on data type of search? like first int(salary) and next is varchar(name) ? if we reverse will performance change ?
Upvotes: 0
Views: 116
Reputation: 1271231
SQL is a declarative language, not a procedural language.
That is, a SELECT
query describes what the result set should look like. It does not define the step-by-step operations used to satisfy the query. What gets executed is the execution plan. And that is produced by the optimizer after the compilation phase (and some database engines can even tweak the execution plan after the query has started running).
So, you question is about what the optimizer does. For all practical purposes, the optimizer will produce the same execution plan, making use of the same indexes, partitions, table statistics, and so on.
There is one case where there could theoretically be a small difference in performance. If, say, all salaries were 60000, then doing that comparison first is worse than doing it second -- because SQL Server short-circuits AND
calculations, so it stops at the first "false".
I'm not 100% sure if the SQL Server optimizer has enough information to arrange the comparisons, taking into account both the distribution of values in the columns and the complexity of the comparisons, to generate an optimal ordering (it would do this for more expensive comparisons). However, comparisons on base types are pretty fast, so even reversing them would have a very small performance impact.
Upvotes: 0
Reputation: 148
I prefer to use query first
Upvotes: 0