Nested Loop Joins

Bert Wagner walks us through nested loop joins:

Nested loops joins work like this: SQL Server takes the first value from our first table (our “outer” table – by default SQL Server decides for us which table of the two this will be), and compares it to every value in our second “inner” table to see if they match. 
Once every inner value has been checked, SQL Server moves to the next value in the outer table and the process repeats until every value from our outer table has been compared to every value in our inner table.

This description is a worst case example of the performance of a nested loop join.

Read the whole thing.  Understanding physical join operators is a key to figuring out if your data retrieval is as fast as it should be. 

Related Posts

Blaming the Right Cardinality Estimator

Arthur Daniels helps us figure out which of SQL Server’s cardinality estimators your query used: SQL Server 2008 is reaching end of support this year, so upgrading your SQL Server might be on your mind. One of the big changes when you upgrade your SQL Servers is upgrading the compatibility level, which by default will […]

Read More

Optimizer Imperfections With Complex Filters

Erik Darling shows a couple examples of how the optimizer will sometimes pick a superior plan when dealing with complicated filters but not always: Sometimes, the optimizer can take a query with a complex where clause, and turn it into two queries. This only happens up to a certain point in complexity, and only if […]

Read More

Categories

December 2018
MTWTFSS
« Nov Jan »
 12
3456789
10111213141516
17181920212223
24252627282930
31