Introduction
Continuing my
previous article Understanding Execution Plan [ When Table JOIN occurs Part – I
] hare we are describing the others process of Execution Plan.
If have you
not read my previous article, please go through the following link.
For reference
we again have a look at the Execution plan of the flowing Query.
SELECT e.JobTitle, a.City,
p.LastName + ', ' + p.FirstName AS EmployeeName
p.LastName + ', ' + p.FirstName AS EmployeeName
FROM HumanResources.Employee AS e
INNER JOIN Person.BusinessEntityAddress AS bea
ON e.BusinessEntityID =bea.BusinessEntityID
INNER JOIN Person.Address a ON bea.AddressID =a.AddressID
INNER JOIN Person.Person AS p
ON e.BusinessEntityID = p.BusinessEntityID;
ON e.BusinessEntityID = p.BusinessEntityID;
The Nested Loop Join
If we look at
our Execution Plan carefully we find that the second operation from the top
right is a Clustered Index Seek operation (BusinessEntityAddress Table). This
is relatively less expensive (only 9%). The Seek is a part of Join Operation
and we can see the different search criteria with it. For this we have to look
at the Seek Predicates section at the Bottom of the tool tip property.
A Nested
Loops join functions by taking a set of data, referred to as the outer set,
and comparing it, one row at a time to another set of data, called the inner
set. It just likes a cursor, and effectively, it is one but, with the
appropriate data set, it can be a very efficient operation.
The data Scan
at Employee Table and the Seek against the BusinessEntityAddress table is join
at Nested Loop Join operation. To understand it properly we have to look at the
tooltip or property of the Nested Loop Join.
We can call
Nested Loop as Nested Iteration as the operation takes input from two data sets
and join them by scanning from outer data set (Here in our Execution Plan it is
the Bottom operator) once for each row in the inner set. If the number of rows is
in two data set is small, the Nested Loop operation is much more efficient. As long as the inner data set is small and the
outer data set, small or not, is indexed, then this is an extremely efficient
join mechanism. Except in cases of very large data sets, this is the best type
of join to see in an execution plan.
Related Reference
Understanding the Execution
Plan [ When Table JOIN occurs Part-I ]
|
http://www.sqlknowledgebank.blogspot.in/2014/10/understanding-execution-plan-when-table.html
|
Understanding of
Execution Plan – III - C [ The OPERATORS]
|
|
Understanding of
Execution Plan – III - B [ The OPERATORS ]
|
|
Understanding of
Execution Plan [What happened When SQL statement Execute]
|
|
Understanding of
Execution Plan – II [Reuse of the Execution Plan]
|
|
|
|
Summary
In the next level we
have to more discuss about our execution plan. So this series will be continued
for some more articles. Please be with us.
Hope you like it.
Posted by: MR. JOYDEEP
DAS
No comments:
Post a Comment