Saturday, 25 October 2014

Understanding the Execution Plan [ When Table JOIN occurs Part-II ]

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
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;



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