SQL Server Performance

Hash Joins

Discussion in 'T-SQL Performance Tuning for Developers' started by bergshawn, Mar 28, 2003.

  1. bergshawn New Member

    I read in your recent article about hash joins and how if tables are indexed properly you should not see any hash joins. I have tried every combination of indexes on both tables regarding the fields being used in the join and no matter what I do the Hash Join always shows up in the execution plan. I am joining table A to table B using two field comparisons.

    Exaple:
    LEFT JOIN tblB ON tblA.field1 = tblB.field1 AND tblA.field2 = tblB.field2

    I have indexed both fields seperately in tblA and both fields seperately in tblB. That still produces the hash join. I have also tried making one index ecompassing both fields in tblA and the same for tblB, which STILL results in the hash join. Can anyone give me any idea of what I might be doing wrong?

    Thanks,

    Shawn

  2. thomas New Member

    It isn't strictly true to say you won't see any hash joins if your tables are correctly indexed. If you are processing a large number of rows, hash joins can be more efficient than nested loops. It sounds like you have tried all sensible indexing strategies. The question is, do you have a performance problem? If not, don't worry about SQL Server doing hash joins. It may be the best way of executing your join.

    Tom Pullen
    DBA, Oxfam GB
  3. bergshawn New Member

    Yeah the reason I am concerned is because there is a performance issue with the stored proc. It takes about 10-13 seconds to execute for some criteria that produce more data than others. Some execute in under a second. I was trying to speed it up so it would execute in a few seconds tops for every criteria. Out of all the queries in the SP only one had two hash joins, and I eliminated one hash join, just can't get rid of the other.

    Shawn
  4. thomas New Member

    Perhaps you should try forcing a loop join by using the loop optimiser hint. Then you will know if it is the hash joing which is slowing you down.

    Tom Pullen
    DBA, Oxfam GB
  5. bradmcgehee New Member

    I have to agree with thomas that my statement on my website about hash joins was probably a little too exacting. Yes, there are some exceptions when a hash will be faster than a loop join, although this is not common. As thomas has suggested, see what happens when you force a loop join using a hint. If it works, then great. If not, then perhaps there is something else that is hurting the performance.

    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com

Share This Page