Database Reference
In-Depth Information
Comparing Join Types
As usual, the choice of the join operator fits into the “It Depends” category. Each join type has its own pros and cons,
which makes it good for some use-cases and not so good for others.
Table
25-2
compares different join types in various scenarios.
Table 25-2.
Join types comparison
Nested Loop Join
Merge Join
Hash Join
Best use-case
Small inputs. Preferable with index
on join key in inner table.
Medium to large inputs
sorted on index key.
Medium to large
inputs.
Requires sorted input
No
Yes
No
Requires equality
predicate
No
Yes
Yes
Blocking operator
No
No
Yes (Build phase only)
Uses memory
No
No
Yes
Uses tempdb
No
No (with exception of
many-to-many joins)
Yes in case of spills
Preserves order
Yes (outer input)
Yes
No
One of the common mistakes people make during performance tuning is relying strictly on the number of logical
reads produced by the query. Even though that number is a great performance characteristic, it could be misleading
in the case of joins. For example, it is entirely possible that a hash join produces fewer reads as compared to a nested
loop. However, it would not factor in CPU usage and memory overhead or the performance implication in the case of
tempdb
spills and bailouts.
The merge join is another great example. While it is more efficient than a nested loop on sorted inputs, it is easy
to overlook the overhead of the
Sort
operation, which often prepares input for the merge join.
As usual, you should keep join behaviors and the pros and cons of each join type in mind, and factor this into
your analysis.
Aggregates
Aggregates perform a calculation on the set of values and return a single value. A typical example of aggregates in SQL
is the
MIN()
function, which returns the minimal value from the group of values it processes.
SQL Server supports two types of aggregate operators:
stream
and
hash
aggregates.
Stream Aggregate
A
stream aggregate
performs the aggregation based on sorted input, for example, when data is sorted on a column,
which is specified in a
group by
clause. Listing 25-10 shows the stream aggregate algorithm.
