Today let’s complete the series reviewing the different join techniques found in SQL Server with the Loop Join. This techniques works well for smaller tables or smaller sets. The join is performed by walking through each record of the first table meeting the users critiera. For each row found it then seeks the joining records in the table to be joined based on the foreign key value from the first table.
If there is an index in the second table based on the join criteria, the loop join can take advantage of that index to locate the appropriate rows. If the index is either the clustered index of the second table, or is a covering index (an index containing all the columns being requested by the output of the query) then the loop can be efficient even on larger sets.
You may find this occurring more often when you are joining two tables and are filtering the data of one table by an attribute different than the one used to join the two tables. For example, if you are joining a department table to an employee table by the departmentID in both, and filtering the department table by region, the query may first filter the list of departments and then for each department selected loop into the employee table finding those with the same departmentID.
One thing that is common for optimizing all join techniques is to maintain updated statistics which are used to determine the most efficient join method. The available, or missing indexes also make a distinct impact on the performance of your queries. Don’t neglect either of these two features essential to query performance.
In short, understand the merge, hash, and loop join techniques, and how they are used to join data. This understanding can help you improve the performance of your queries.
Cheers,
Ben