SQL Server Performance

Order of join conditions

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by atulgoswami, May 26, 2009.

  1. atulgoswami New Member

    I got two different feedback on one case as below: one says join codition should follow the order of columns defined in clustered index and other says it should following the table (relationship) heirarchy.

    Please refer beloe for the scenario
    I have one table (table 3) which has one clustered index on columns (Col1, Col3, Col2). I am intentially writing this sequence as it is not followed according to the heirarchy.
    e.g
    Table1 (level 1 table) - Col1 (primary key)
    Table2 (level 2 table) - Col2 (primary key)
    Table3 (level 3 table) - Col3 (primary key)
    Now when i place inner join condition then do i need to put in the sequence defined in the clustered index (even if it is oncorect) or according to the table level sequence.

    e.g
    t1.col1
    t2.col2
    t3.col3

    Am i making any sense in this?

    It would be of great help if somebody can give some idea on this.
    Thanks
    Aero
  2. Adriaan New Member

    I seem to remember a recommendation for joins on multiple columns, that you should put the least selective column first, and the most selective column last. The reason given for this was that the query optimizer processes join conditions right-to-left.
    Not sure if this is valid.
  3. atulgoswami New Member

    [quote user="Adriaan"]
    I seem to remember a recommendation for joins on multiple columns, that you should put the least selective column first, and the most selective column last. The reason given for this was that the query optimizer processes join conditions right-to-left.
    Not sure if this is valid.
    [/quote]
    Thanks but considering the suggestions i have, which one looks correct or relavent in terms of performance gain. Let me put more information about my scenario:
    I have "User" table which USERID (Numeric) PK,
    another table, which deals with visit of users "UserVisit" and has UserVisitID (Numeric) PK and
    third table which deals with orders as UserVisitOrders has UserOrderID (Numeric) PK
    When i make a join with these three tables then i should follow User -> UserVisit -> UserVisitOrder
    OR
    Follow the order of the column defined in the Clustered index (provided the order is not same as defined above)
    Thanks
  4. FrankKalis Moderator

    As long as you don't specify the FORCE ORDER query hint, the optimizer is free to choose whatever it deems best based on available indices, statistics, etc... as long as the result is guaranteed.
    This includes that it might reorder the JOIN condition A JOIN B ON A.c1 = B.c1 AND A.c2 = B.c2 when there is an index on the column in opposite order (c2. c1) to make use of that index.
    Edit: Of course in case of INNER JOINs this might also include rearranging the order in which the tables are accessed.

Share This Page