SQL Server 6.5: Nested-Loop Joins

August 17, 2000


Nested-Loop join is the only one kind of a join in SQL Server 6.5 in comparison with Nested-Loop joins, Merge joins and Hash joins in SQL Server 7.0.

If you join two tables, then one table will be selected as outer table and the other as the inner table. When executing a nested-loop join, SQL Server will scan the outer table row by row and for each row in the outer table, it will scan the inner table, looking for matching rows. To increase the speed of this join operation, you can create an index on the column that joins the tables. SQL Server can change the join's order of the joined tables to increase performance. There are some situations:

1. There is no index on the column that joins the tables neither in the outer table, nor in the inner table.

In this case, don't care how to join these tables, because all tables will be completely scanned to find the appropriate rows.

2. Only one of the tables has an index on the column that joins the tables.

In this case, SQL Server optimizer will set the table with index as an inner table, and the other table as an outer table.

3. All tables has an index on the column that joins the tables.

In this case, SQL Server optimizer will set the bigger table (table, which occupies more data pages) as inner table, and the other table as outer table.

To see how many data pages the table occupies, you can use DBCC SHOWCONTIG command.

This is the example:

USE pubs
DECLARE @tbid int
SELECT @tbid = object_id('authors')
DBCC SHOWCONTIG (@tbid)

These are the results from my machine (see Pages Scanned):

[SHOW_CONTIG - SCAN ANALYSIS]                                    
-----------------------------------------------------------------
Table: 'authors' (16003088)  Indid: 1  dbid:5
TABLE level scan performed.
- Pages Scanned................................: 1
- Extent Switches..............................: 0
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 100.00% [0:1]
- Avg. Bytes free per page.....................: 60.0
- Avg. Page density (full).....................: 97.02%
- Overflow Pages...............................: 0
- Disconnected Overflow Pages..................: 0

If you need to join more than two tables, the above rules are also present. The maximum number of tables in one select statement is 16, so there is no documented way to avoid this restriction in SQL Server 6.5.

The query optimizer of SQL Server 6.5 will produce ranked samples from n joined tables by 4 and will define amongst them optimum. Outer table of this sample is considered the most outer in the final order and from further consideration will be excluded. This procedure will repeat for n-1 tables, then n-2 and etc. If you will enable the 345 trace flag, then query optimizer will consider by 6 tables simultaneously, that brings about increasing a time to compiling, but raises accuracy of choice of optimum order.


» See All Articles by Columnist Alexander Chigrik









The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers