Understanding the Execution Plan [When Table JOIN occurs Part-II]
Join the DZone community and get the full member experience.Join For Free
Continuing my previous article Understanding Execution Plan [ When Table JOIN occurs Part – I ], here we are describing the other process of Execution Plan.
If have you not read my previous article, please go through the following link.http://www.sqlknowledgebank.blogspot.in/2014/10/understanding-execution-plan-when-table.html
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.AddressaONbea.AddressID=a.AddressID INNER JOIN Person.PersonASp 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.
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.
|Understanding the Execution Plan [ When Table JOIN occurs Part-I ]|
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]
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.
Published at DZone with permission of Joydeep Das, DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.