How come Hash is faster than the merge even though the join is on the clustered index

GokhanVarol 2013-06-06 01:24:45

How come Hash is faster than the merge even though the join is on the clustered index keys and the merge plan has no sort?
By mistake I put the on clause not in the exact clustered key order and I end up getting a hash plan, then I said yes I can improve the query just by fixing the join order and get a merge join instead, I did get a merge join but it's performing poorly, why would that be?
Thank you

link text
alt text

SQLkiwi 2013-06-06 06:45:00
It's hard to be certain without being able to analyse the queries myself on a local machine, but there are some general points I can make:

Parallel merge join is an algorithm that does not scale all that well, particularly as DOP rises. Parallel hash join scales much more easily, and typically has the lowest per-row cost of the three physical join types (though it does require a memory grant). The parallel hash plan would likely be even better if the spill could be avoided (though requiring even more than the current 741MB of memory).

The repartitioning exchanges in the merge join plan are merging exchanges; they preserve input order, as shown by the Order By attributes on those operators. Preserved ordering is another reason that parallel merge does not scale. Even where a parallel deadlock does not actually occur, delays associated with the ordered data do accumulate. The delays would be likely to show up as increased CXPACKET waits in the merge plan, though you would need the Pro version of Plan Explorer to see that in the analysis file.

Per-partition parallel merge join could scale better in suitable cases (giving a similar plan shape to co-located parallel hash join) but SQL Server does not provide that facility.

Parallel merge join does not always have performance problems (it depends on data distribution and other factors), and parallel hash join will not always be faster. The query in question is an unusual one, so please don't draw too many conclusions about general performance from it. All the joins types have strengths and weaknesses, parallelism or no.

GokhanVarol 2013-06-06 16:33:11
The clustered index order is CntyCd, PclId, PclSeqNbr, BldgSeqNbr

I do not understand why the first one creates a hash :
ON l.[BldgSeqNbr] = r.[BldgSeqNbr] AND l.[CntyCd] = r.[CntyCd] AND l.[PclId] = r.[PclId] AND l.[PclSeqNbr] = r.[PclSeqNbr]

… and the second one creates a join, arent't they the same thing?
ON l.[CntyCd] = r.[CntyCd] AND l.[PclId] = r.[PclId] AND l.[PclSeqNbr] = r.[PclSeqNbr] AND l.[BldgSeqNbr] = r.[BldgSeqNbr]

Thanks

SQLkiwi 2013-06-07 00:27:49
Yes they're the same thing. Ensure you have plan-affecting optimizer fixes enabled with TF 4199.
GokhanVarol 2013-06-06 16:37:40
I ran the same 2 queries twice with Plan Explorer Pro on my work maching which is I7 xeon vs only I7 at my home, more than enough available memory on both machines and I believe the SSD I have at home is OCZ vertex 4 (raid 0 2x256GB) and some Samsung SSD (512 GB) at my work.
At my work the gap between merge and hash is a lot less but hash is still about 10% faster.
Is there anything I can tweak in the merge join to make it more efficient?
Thank you

link text

SQLkiwi 2013-06-07 00:31:01
Fundamentally, no; it is what it is.