Over a million developers have joined DZone.

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

· Java Zone

Discover how AppDynamics steps in to upgrade your performance game and prevent your enterprise from these top 10 Java performance problems, brought to you in partnership with AppDynamics.


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.


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.

Related Reference

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]


Understanding of Execution Plan – III - A [ The OPERATORS ]



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.

The Java Zone is brought to you in partnership with AppDynamics. AppDynamics helps you gain the fundamentals behind application performance, and implement best practices so you can proactively analyze and act on performance problems as they arise, and more specifically with your Java applications. Start a Free Trial.


Published at DZone with permission of Joydeep Das, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}