question

samiigom avatar image
samiigom asked

INNER JOIN Vs. INNER HASH JOIN

Hi, We have recently migrated to SQL Server 2008 from 2000. When I run queries having INNER JOIN they took long time to run on 2008 box but when I replace INNER JOIN with INNER HASH JOIN the queries get executed faster. Can anybody help me to understand this and what is the resolution on this? Regards, Samiigom.
joins
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

can you post the script you were using so that we can make a better effort at explaining why you get these conditions?
1 Like 1 ·
WilliamD avatar image
WilliamD answered
It sounds a little like you are having problems with table and index statistics possibly being outdated. You say that you migrated from SQL 2000 to 2008. Did you perform DBCC checks and update all statistics or rebuild indexes after the migration? By adding the query hint HASH to the join, you forced SQL Server to process the data in a certain way, which just so happened to fit nicely and give you your results faster. As Fatherjack wrote, please provide the script (table creation and query) along with the query plan if possible, so that we can give you some pointers.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image
Matt Whitfield answered
Just to comment on what a HASH join is. There are basically three types of join - HASH, MERGE and LOOP. Hash is whereby a hash code is used as an O(n) lookup into the other rowset. This one is good for large, unordered data sets. Merge is whereby rows are 'consumed' from each side of the join in the correct sequence - meaning that each side must be sequenced. This one is good for any size of ordered data set. Loop is whereby each row is matched against every other row from the other side of the join. Imagine knowing the title of a book, then comparing the title with every book in the library. While this sounds hurrendous, it will actually be more efficient for small unordered data sets than a hash join. Just FYI.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Nice elaboration on the join types. +1
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.