SQL Server Performance

Hashmatch with left Outer Join

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

  1. atulgoswami New Member

    Hi,

    I am joining two tables on the clumns which are covered in non clustered index. Both the tables have clustered index.

    When i make left outer join then it performs hashmatch operation and when i make inner join then it performs nested loop.

    I am executing the query on a test database which hardly used to go under any change and by this i guess there will not be any problem with statistics.

    I did not try rebuilding the index but i dont think so there is a problem with index.
    I dont know why it is happening and on top of it am i making any sense in this observation.
    Any idea/suggestion would be of great help.
    Thanks
  2. preethi Member

    Hi,
    This seems to be an interesting issue.. Can you post the table structure along with indexes so that we can try and figure out whats going on?
  3. atulgoswami New Member

    Table1: CCLVST
    (
    CLID NUMERIC (16,0)
    CLGID NUMERIC (16,0),
    CLCHID NUMERIC (16,0),
    CLName Varchar (100)
    -
    -
    -
    )

    Index:
    NC Index 1 (CLCHID)
    C Index 1 (CLGID, CLID)
    NC Index 2 (CLID)

    Table2: CLPHYNT
    (
    CLPHID NUMERIC (16,0),
    CLGID NUMERIC (16,0),
    CLCHID NUMERIC (16,0),
    PHYNT VARCHAR(100),
    )

    Index:
    NC Index 1 (CLPHID)
    C Index 1 (CLGID, CLCHID)

    SELECT C.CLName, P.PHYNT
    FROM
    CCLVST C LEFT OUTER JOIN CLPHYNT P ON
    C.CLGID = P.CLGID AND
    C.CLCHID = P.CLCHID

    Please check this if it makes any sense.
    Thanks
  4. Adriaan New Member

    An inner join is usually a more 'economical' operation, in that the database engine only needs to consider matching rows, and can safely ignore all others.
    Why does your query need an outer join? If you are deleting rows for which there is no matching row on another table, then you might try using a NOT EXISTS correlated subquery in the WHERE clause, instead of the outer join.
  5. atulgoswami New Member

    Why i am user left outer join because it is my requirement to display the data in reports. visit table maintains all visits and note table maintains some 20 attributes per visit. It may be possible that every visit does not record the data for all 20 notes. So need to display all notes per visit whether entered or not.

    I think it is happening because of difference in number of rows in both the tables. Per user, visit table can have n number of visits and each visit, need to enter attributes.

    note table has roughly 10 times data as of visit table. But this is a problem for as it is scanning more rows.

    May be you can comment on Index structure as visit table has different index structure than that of note table for two columns used in join. One has both the columns in same index and other has two different index for two different columns.
    Thanks
  6. preethi Member

    Sorry for the delay in replying. (I started working on it but got carried away with other thigns :)
    There were two issues I see:
    1. The rows should be quite high on CLPHYNT
    2. The index differences cause the first table to give the data in different order.
    My suggestion is the have a non clustered index on CCLVST on columns CLGID and CLCHID with an include of CLName.
    This suggestion may make your inserts, updates and deletes to be slower if you have many such activities on CCLVST

Share This Page