CODEWITHSUNDEEP
sqlsql-serverdatabasesql-server-2016
Uday Kumar B R
Uday Kumar B R

Reputation: 3

which query runs faster on below listed SQL queries

Employee Table

  1. select * from Employee where Salary=60000 and FirstName = 'Mark'

  2. 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

Answers (2)

Gordon Linoff
Gordon Linoff

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

Reza Yousefi
Reza Yousefi

Reputation: 148

  • from Actual Execution plan is equal
  • from statistics io is equal
  • from statistics time is equal

I prefer to use query first

Upvotes: 0

Related Questions