This post is part 2 in a series about physical join operators, please be sure to check out:
The Nested Loops operator is one of four operators in charge of combine data and generate an output stream.
The Nested Loops operator is by far the simplest algorithm, let’s take a look at its visual appearance.
How to read plan above?
Everyone has their own way to read execution plans, but generally we have to read execution plans from right to left, following the data flow arrows. Therefore, in the image above we see two data flows arrows passing data to the Nested Loops, the one in the top is called Outer Input the one in the bottom is called Inner Input, then to the left of the Nested Loops we see the output stream (the combined data).
How it works?
The logic is very simple to understand, since it’s like two nested cursors, the main cursor is the data from the Outer Input and the nested cursor is the data from the Inner Input. So take the first value from the Outer Input, and compare it to each value in the Inner Input (nested cursor), once the first loop is completed then take next value from Outer Input and compare to each value in the Inner Input, and so on. Please see below.
Can Nested Loops seek?
In fact, there are several optimizations that allow a Nested Loops to seek directly to the rows, for example (but not limited to):
Cluster Index
Nonclustered Index
Spool
Do you wonder how this works? Please see below.
SQL Server still loops through the Outer input one row at a time, but it does not loop through the Inner input anymore, however, the seek was executed four times (for each row in the Outer Input)
Considerations.
If Outer Input is small (low cardinality) and Inner Input is cheap to loop or seek (low cost), then Nested Loops will be very efficient.
Right indexes allow to seek through the Inner Input.
Under normal circumstances the Nested Loop is a not blocking operator.
Last, but not least, I want to clarify that the main purpose of this post is to explain in the simplest way possible the way in which the Nested Loops operator does it job, this is the reason why I did not cover other cool stuff( Dynamic vs Static Inner inputs, Prefetching, Rebind and Rewinds, etc).
Enjoy!!
Comments