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.
can you post the script you were using so that we can make a better effort at explaining why you get these conditions?
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.
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.
