SQL Server Performance

Hash Join

Discussion in 'Performance Tuning for DBAs' started by bfarr23, Dec 3, 2003.

  1. bfarr23 New Member

    I have read that hash joins usually occur because there are no proper indexes on one or both tables involved in the join.

    Currently I have a hash join where the two tables involved use a clustered-index seek and the cost is the majotity of the whole query.

    Is there any way to convert this to a merge or nested loop?

  2. bradmcgehee New Member

    You can experiment with adding a hint to force a nested loop join, and see what that does to the execution plan and performance. In some cases, hash joins can be more efficient than nested loop, but not very often. This experiment will tell you which join is better, and if the Query Optimizer is providing you good data or not.

    Brad M. McGehee, MVP
  3. joechang New Member

    there is nothing wrong with a hash join if it is properly used, and not inadvertent

    hash and merge joins are best if:
    1) the entire table is involved, ie, no SARG
    2) a SARG is specified on both tables and good indexes are available

    SQL Server has a really silly cost formula for loop joins in certain circumstance where the estimated row count is more than 130 rows.
    this makes it think that the hash is more expensive.
    in any case, the estimated cost shown in the plan is very poorly calibrated on modern systems, so that alone is not a reason to worry,
    if your hash join has a table or index scan on one or both tables when a good index is available, that is a reason to worry
    if your join has less than 500 rows,
    you can use a LOOP join hint

Share This Page