SQL Server Performance

WHERE or INNER JOIN

Discussion in 'T-SQL Performance Tuning for Developers' started by hipriti, Nov 16, 2005.

  1. hipriti New Member

    I have a query that takes over 6 minutes to fetch 4500 records. It has several INNER JOINS. If I replace them with WHERE clause, will it be faster? I am using MS SQL Server 7.0 and SQL 2000.

    How does OPTION (LOOP JOIN) affect the performance of a query? The query looks like:-

    SELECT table1.* FROM
    ((((table1 INNER JOIN table2 ON table1.a=table2.b ) INNER JOIN table3 ..... )))
    ORDER BY x,y,z
    OPTION (LOOP JOIN)
  2. satya Moderator

    If you join two tables, and SQL Server optimizer choose Nested-Loop join, then one table will be selected as outer table and the other as inner table. SQL Server will scan the outer table row by row and for each row in the outer table, it will scan the inner table, looking for matching rows.

    Nested-Loop join will be chosen if one of the tables is small and the other table has an index on the column that joins the tables.

    The query optimizer usually selects the best execution plan for a given select statement, it is not necessary to change the kind of join, but sometimes it can be useful. You can enforce the desirable join type with OPTION clause.

    http://www.sql-server-performance.com/hints_join.asp
    http://www.sql-server-performance.com/query_execution_plan_analysis.asp
    Fyi.

    Satya SKJ
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. benwilson New Member

    To the best of my knowledge, changing from INNER JOINS to a series of where statements will not improve performance (i believe INNER JOIN is the ANSI standard)

    Regarding the Loop Join- personally, i would leave it up to SQL server to decide what kind of join to use.

    It might be a good idea to make sure you have good indexes on the tables you are joining to help improve performance

    'I reject your reality and substitute my own' - Adam Savage
  4. FrankKalis Moderator

    SQL Server will treat


    SELECT blabla
    FROM a,b
    WHERE a.col = b.col

    and


    SELECT blabla
    FROM a
    INNER JOIN b
    ON a.col = b.col

    equally and will produce identical execution plans. Both are valid statements and comply to the ANSI standard. Personally I favor the explicite INNER JOIN variante.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Heute schon gebloggt?http://www.insidesql.de/blogs
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  5. null New Member

    Another side effect of specifying an option for the join type is I believe it could also change the order in which sql server tries to join the tables, which could have a negative impact on performance as well. I know this to be true when you use join hints to change the join type in this manner...


    select ...
    from tableA
    inner loop join tableB on(...)
    inner loop join tableC on (...)

    ...but I'm uncertain if this is true for specifying join type w/option. Regardless, take out the option and compare the exec plans, and see if the qry runs any faster.
  6. Madhivanan Moderator

    http://sql-server-performance.com/forum/topic.asp?TOPIC_ID=11286

    Madhivanan

    Failing to plan is Planning to fail
  7. druer New Member

    6 minutes to complete a query for that few rows seems like an exceptionally long time so I don't want to miss the obvious, you need to make sure that you have indexes on the fields being joined and any fields that you are using in WHERE clauses to speed things up. Have they been defragmented and or rebuilt lately, or are they a mess?


Share This Page