Nested Loop Join


There are basically three join mechanisms in SQL Server:

i. Nested Loop Join
ii. Hash Join
iii. Merge Join

The Nested Loop Join is chosen by the SQL Server when the tables being used are of the comparable sizes and is been the default join mechanism being used.

It is non-blocking,  doesn't require equi-join, data to be sorted and doesn't use tempdb.

It is mostly suited for inner joins between tables having relatively smaller inputs and having an index on the joining key.

Select a.id, b.id
from Table1 a
inner Join Table2 b
on a.id=b.id

If we look at its execution plan, it will be using Nested Loop join.

Also, In cases where we are having cross join between two tables mostly Nested Loop join will be used by the SQL Execution plan as it is a product join and produces N*M rows. In cases, if we add certain where clause to the Cross Join query to limit our results, Nested Loop Join might not be used.

Basic Example:

There is a table with four columns:

Table A: Col1 int , Col2 int , Col3 int , Col4 int

There is a clustered index on Col1 and non-clustered index on Col4.

If we perform the query :
Select Col1, Col2, Col3, Col4 from Table A where col4= 9664

If we look at the execution plan, it will be using Key lookup as there are no indexes for col2 and col3 and the table will fetch the values from the clustered index and there will be nested loop join being used in addition to index seek.

But when we create a covering index in order to cover col2, col3 as well in our results we get better performance, and the execution plan reduces to mere one index seek, this improves performance greatly.

Performance with Table Variable: 

 Whenever we are performing an inner join operation with table variable as inner table, Nested loop join will be used as SQL Server assumes its Estimated row size to be 1. So, if we are having a large number of rows in a table variable the performance will degrade as there are no statistics that are being maintained in case of a table variable.

Video Reference:

https://sqlmaestros.com/nested-loop-join-sql-server-satya-ramesh/



Comments

Post a Comment

Popular posts from this blog

Query Execution Plan - Basics

There are two types of Execution plan in SQL Server i.e. Estimated execution plan Actual execution plan Estimated execution plan is generated based on the statistics available for the SQL table columns and indexes. The estimated execution plan may differ from the actual execution plan as the latter is generated when the query is executed and depicts the execution mechanism followed by the server in order to generate the query in the best possible way (least time). Execution plan is the steps taken by the SQL server optimiser to reach the SQL query results in best possible way. We always have questions such as why our query is running slow, how we can reduce the time to execute the query. The query which is to be executed is submitted to the SQL Server, the query optimiser is responsible for parsing the query and generating the execution plan. When the query is submitted to the system, there are many underlying operation that are happening in the backend before the result is...
Read more