Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Aug 18, 2000

SQL Server 6.5: Nested-Loop Joins

By Alexander Chigrik

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')

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

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM