Overview
There are three main physical connection methods for databases: Nested join, merge join, and hash join. This article mainly talks about the nested join (NL). In NL, the two connected sets are respectively outertable and innertable.
Select * FromOrderdetailsAsOdInner Join [Order] AsOOnOD. order_id=O. ID
Here, the Order table is outertable and the orderdetaiils table is innertable. However, the database optimizer selects a small table as the outertable
SimpleAlgorithmDescription
First, retrieve a row in outertable and compare it with each row in innertable. If the row meets the conditions, return. Then outertable continues to fetch the next row, and compares it with each row in innertable...
PseudoCode(From http://blogs.msdn.com/ B /craigfr/archive/2006/07/26/nested-loops-join.aspx)
For each row R1 in the outer table
For each row R2 in the inner table
If R1 joins with R2
Return (R1,
R2)
In the most primitive mode, the cost of the algorithm = the number of outertable rows * The number of innertable rows
SQL ServerNo optimized nested join applied in
Test Data
View code
If Exists ( Select * From SYS. Objects Where Object_id = Object_id (N ' [DBO]. [order] ' )) Drop Table [ DBO ] . [ Order ] Go -- Order table Create Table DBO. [ Order ] (ID Int , Cus_name Nvarchar ( 50 )); Insert Into [ Order ] Select 1 , ' Mike ' Union All Select 2 , ' Ben ' Union All Select 3 , ' Clare ' If Exists ( Select * From SYS. Objects Where Object_id = Object_id (N ' [DBO]. [orderdetails] ' )) Drop Table [ DBO ] . [ Orderdetails ] Go -- Order Details Create Table DBO. [ Orderdetails ] (ID Int , Order_id Int , Good_name Nvarchar ( 50 )); Insert Into Orderdetails Select 1 , 1 ,' Hard Disk ' Union All Select 2 , 1 , ' Display ' Union All Select 3 , 2 ,' IPod ' Union All Select 4 , 4 , ' Food '
Execute SQL
-- Set statistics profile on: displays the time required for analysis, compilation, and query execution. -- For more information, see http://www.cnblogs.com/qanholas/archive/2011/05/06/2038543.html. Set Statistics Profile On Select O. ID, O. cus_name, OD. good_name From Orderdetails As Od Inner Join [ Order ] As OOn O. ID = OD. order_id Option (Loop Join ) -- Force optimizer to use nested join
Result
We can see that
1> when running SQL Server (SQL 2008 is used for testing), the system automatically selects a small table as the outertable and a large table as the innertable;
2> the third row displays the outertable information, indicating that outertalbe only needs to be scanned once. The fourth row displays the innertable information, indicating that innertable needs to run n (n = the number of outertable rows) times, and returns N * m (M = innertalbe rows)
SQL ServerApply the nested join after INDEX OPTIMIZATION IN
Test data: continue to use the previous test data
Create an index: Create clustered index odoid on [orderdetails] (order_id)
Execute SQL:
Set StatisticsProfileOnSelectO. ID, O. cus_name, OD. good_nameFromOrderdetailsAsOdInner Join [Order] AsOOnO. ID=OD. order_idOption(LoopJoin)--Force optimizer to use nested join
Result:
We can see that
After innertable adds an index to the connected field, although outertable executes n times, innertable returns only the valid row for each outertable loop. Before the index is added, innertable needs to return all rows for each outertable loop, and then compare whether the join conditions are met.
Summary
Nested join applies to a small set (less than 2000 rows... As you can see on the Internet, you can practice it on your own.) As an outertable, a large collection (which can be larger than millions, and a large collection must be indexed on the connection conditions ), when these two sets are connected, you can use nested join.
Refer:
Http://www.cnblogs.com/RicCC/archive/2007/06/26/SQL-Server-Performance-Tuning-Nested-Loop-Merge-Hash-Join.html
Http://blogs.msdn.com/ B /craigfr/archive/2006/07/26/nested-loops-join.aspx