SQL Server Performance Forum – Threads Archive
Hash Joins
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
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
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
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
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
]]>