A nested loop join chooses one table as the outer input and searches the other, inner input for matching rows for each outer key. It is most efficient for small tables, but forcing a different operator after one slow plan can work against the optimizer. section 10.5
Avoid the memorable mistake of treating one slow plan as proof that nested loops are always wrong:
- Inspect the query plan before prescribing a join operator. section 10.5
- Treat query hints as a last resort; first consider remedies such as updating statistics. section 10.5
- Reconsider the plan after adding indexes, because changed inputs can change the optimizer’s best choice. section 10.5
Marketing impressions
A join between Marketing.marketing.Impressions and MarketingArchive.dbo.ImpressionsArchive used nested loops and took 19 seconds on the test machine. Forcing a hash join reduced that test to 5 seconds, but the safer lesson is to investigate statistics, indexes, and plan changes rather than permanently forcing the apparent winner. section 10.5
Table 10.1 Physical join operators (view table figure)
| Operator |
Description |
Most efficient use |
|---|---|---|
| Nested Loops |
Choose one table as the outer input table and the other table becomes the inner input table. For each key in the outer input table, it searches every row in the inner input table for matching key values. |
Small tables |
| Hash Join |
Builds a hash table in memory, with each row of the first table placed in a hash bucket, dependent on the key value. The keys of the second table are then hashed and compared to the first table on a row-by-row basis. If there is not enough memory for every row in the first table, then the operation will be performed in multiple steps. |
Large tables not sorted by join key or where inputs have a big difference in row count |
| Merge Join |
Checks the equality of the first key in both tables. It then compares the next row in the second table to the first row in the first table. This is similar to nested loops, but because the inputs are sorted by the join key, it can stop as soon as it finds a row in the second table that is not matched and move to the next row in the first table. |
Large tables sorted by join key, where each table is a similar size |
| Adaptive Join |
The query starts by using a hash join but can change to nested loops during execution if the table that populates the hash table is small enough for nested loops to be more efficient. |
Queries where the input rows wildly swing and Batch Mode execution is used |
Compare nested loops with hash and merge joins, including the input sizes and ordering each operator suits. section 10.5
