Database Reference
In-Depth Information
To understand how the use of
JOIN
hints can affect performance, consider the following
SELECT
statement:
SELECT s.[Name] AS StoreName,
p.[LastName] + ', ' + p.[FirstName]
FROM [Sales].[Store] s
JOIN [Sales].SalesPerson AS sp
ON s.SalesPersonID = sp.BusinessEntityID
JOIN HumanResources.Employee AS e
ON sp.BusinessEntityID = e.BusinessEntityID
JOIN Person.Person AS p
ON e.BusinessEntityID = p.BusinessEntityID;
Figure
18-12
shows the execution plan.
Figure 18-12.
Execution plan showing choices made by the optimizer
As you can see, SQL Server dynamically decided to use a
LOOP JOIN
to add the data from the
Person.Person
table
and to add a
HASH JOIN
for the
Sales.Salesperson
and
Sales.Store
tables. As demonstrated in Chapter 6, for simple
queries affecting a small result set, a
LOOP JOIN
generally provides better performance than a
HASH JOIN
or
MERGE
JOIN
. Since the number of rows coming from the
Sales.Salesperson
table is relatively small, it might feel like you
could force the
JOIN
to be a
LOOP
like this:
SELECT s.[Name] AS StoreName,
p.[LastName] + ', ' + p.[FirstName]
FROM [Sales].[Store] s
JOIN [Sales].SalesPerson AS sp
ON s.SalesPersonID = sp.BusinessEntityID
JOIN HumanResources.Employee AS e
ON sp.BusinessEntityID = e.BusinessEntityID
JOIN Person.Person AS p
ON e.BusinessEntityID = p.BusinessEntityID
OPTION (LOOP JOIN);
When this query is run, the execution plan changes, as you can see in Figure
18-13
.
