SQL Server Performance

Order of Tables in FROM section in SQL

Discussion in 'T-SQL Performance Tuning for Developers' started by Jeff Smith, Dec 18, 2002.

  1. Jeff Smith New Member

    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


  2. bradmcgehee New Member

    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
  3. Chappy New Member

    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.
  4. sqljunkie New Member

    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.
  5. arb New Member

    quote:Originally posted by Jeff Smith


    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,
    ...
    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!

    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.

Share This Page