The Value of Merge-Join and Hash-Join in SQL Server
Goetz Graefe · 1999
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. 1.0 Introduction For a long time, most relational database systems employed only nested loops join, in particular in the form of index nested loops join, and merge join. The general rule of thumb, ...