SQL Server Performance Forum – Threads Archive

Order of Tables in FROM section in SQL

Hi, I recently discovered a few things that blew away everything I had been
taught about SQL performance. I was always taught that the column list in a SQL statement had no bearing
on the performance. I recently found that removing one column (but leaving
the from and where section as is) changed the processing time from 11
minutes to 3 seconds. I can now explain this because I now know that the
data pages no longer had to be read to obtain the value of that column (the
joins used the indexs). The second (and more puzzleing) discovery involves the order of tables in
the FROM section of a SQL statement. Again, I was always taught that this
didn’t matter. Take the following SQL: Select Distinct
…
From table1,
table2,
table3,
table4,
table5,
table6,
table7,
table8,
table9,
table10
Where
…
table4.table8_id *= table8.table8_id
…
Table4 is involved in an outer join with table8. If I move the ‘table4’
table in the FROM section to be the last table in the list, the processing
time decreases from 11 minutes down to 3 seconds! Does anyone no why this is the case ? Thanks, Jeff

---

The order of columns in a SELECT clause of a query, such as "SELECT name, address, city, state", don’t make any difference in performance, but the order of columns or tables in the FROM and WHERE clause can sometimes make a difference in performance. I have seen this before, but I don’t have any rules of thumb on how to order them for best performance, other than to perform trial and error. Anyone else have any good rules of thumb?
——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com

---

Thanks for highlighting the info about the column list and pages being hit. I was not aware of this, but it makes good sense now you mention it.

---

I’ve seen a query plan change, in terms of Index Seek vs. Index Scan by the order of the columns in an index versus the where clause. I would look at the query plan to see if that changed in your example where the execution time when from 11 to 3 seconds. For example:
If you have a table Tbl1 cols a,b and c
With index Idx1(a,b)
The following statement
SELECT * from Tbl1 where b = 1
will do an Index Scan With index Idx1(b,a)
The same select statement will do an Index Seek, which is typically more efficient than an Index Scan.

---

How is the performance affected if you use ANSI JOINs instead? ie, Try re-writing your query to: Select Distinct
…
From table1
INNER JOIN table2 ON …
LEFT JOIN table3 ON … I have only found a couple of rare cases where the order of tables in a query will affect the query plan used. SQL generally is able to optimise the table join order quite well, and I have only had to over-ride it once (using query hints) in order to get best results. Also, the column order is not relevant, but the columns present in your query are, as they will determine whether or not bookmark lookups will be required. plus the DISTINCT keyword can cause your query to run slower than it needs to as the results must be sorted and filtered. If you do not need the distinct clause, getting rid of it could help.

---

]]>