Sladescross's Blog

Blogging about Sharepoint related stuff

Understanding and Optimising SQL Joins January 29, 2011

Filed under: SQL Join Understanding and Optimising,Uncategorized — sladescross @ 6:38 pm

set statistics profile on

Consider this query:

select *

from Sales S inner join Customers C

on S.Cust_Id = C.Cust_Id

option(loop join)

I’ve added a “loop join” hint to force the optimizer to use a nested loops join.  We get this plan which I captured by running the query with “set statistics profile on”:

Rows Executes  
3 1   |–Nested Loops(Inner Join, WHERE:([C].[Cust_Id]=[S].[Cust_Id]))
3 1        |–Table Scan(OBJECT:([Customers] AS [C]))
12 3        |–Table Scan(OBJECT:([Sales] AS [S]))

The outer table in this plan is Customers while the inner table is Sales.  Thus, we begin by scanning the Customers table.  We take one customer at a time and, for each customer, we scan the Sales table.  Since there are 3 customers, we execute the scan of the Sales table 3 times.  Each scan of the Sales table returns 4 rows.  We compare each sale to the current customer and evaluate whether the two rows have the same Cust_Id.  If they do, we return the pair of rows.  We have 3 customers and 4 sales so we perform this comparison a total of 3*4 or 12 times.  Only 3 of these comparisons result in a match.—merge-join/

Simply put, a join is an operation that links one table to another, and SQL Server can use three algorithms to perform this operation, the Loop, Merge and Hash.

The merge join performs an inner join, an outer join or in some cases even a union operation. The merge join is very fast because it requires that both inputs are already sorted by the respective key columns. To do an analogy with the joins I’ll use the same example that I like to show in my presentations.

About these ads

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Get every new post delivered to your Inbox.

Join 63 other followers