The Value of Merge-Join and Hash-Join in SQL Server

Microsoft SQL Server was successful for many years for transaction processing and decision support workloads with neither merge join nor hash join, relying entirely on nested loops and index nested loops join. How much difference do additional join algorithms really make, and how much system performance do they actually add? In a pure OLTP workload that requires only record-to-record navigation, intuition agrees that index nested loops join is sufficient. For a DSS workload, however, the question is much more complex. To answer this question, we have analyzed TPC-D query performance using an internal build of SQL Server with merge-join and hash-join enabled and disabled. It shows that merge join and hash join are both required to achieve the best performance for decision support workloads.

• Type: Conference
• Year: 1999
• Where: VLDB
• Authors: Goetz Graefe
• Keywords: Database, Decision Support Workloads, Merge Join, Nested Loops, VLDB 1999