shannonjk (3/13/2013)
That is odd that the merge should be faster than the hash, since if I add hash to the join (i.e LEFT HASH JOIN) it executes in half the speed...Both systems have the exact same data (well the test system is about a week old, but only 1% different?) I am restoring to test now to test the exact same data, but nothing has change in the last week to warrant any performance issue changes.
Grant, I checked the server default options and they are both the same for each server.
I don't know, merge is the one of the most efficient join operations if the data supports it. Different data means different statistics. That can lead to different plans. What about the SELECT properties on each. Are there differences?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning