Database Reference
In-Depth Information
Figure 18-13.
Changes made by using the
JOIN
query hint
Here are the corresponding
STATISTICS IO
and
TIME
outputs for each query.
With no
•
JOIN
hint:
Table 'Person'. Scan count 0, logical reads 2155
Table 'Worktable'. Scan count 0, logical reads 0
Table 'Store'. Scan count 1, logical reads 103
Table 'SalesPerson'. Scan count 1, logical reads 2
CPU time = 0 ms, elapsed time = 48 ms.
JOIN
hint:
•
With a
Table 'Person'. Scan count 0, logical reads 2155
Table 'SalesPerson'. Scan count 0, logical reads 1402
Table 'Store'. Scan count 1, logical reads 103
CPU time = 16 ms, elapsed time = 73 ms.
You can see that the query with the
JOIN
hint takes longer to run than the query without the hint. It also adds
overhead to the CPU. And you can make this even worse. Instead of telling all hints used in the query to be a
LOOP
join,
it is possible to target just the one you are interested in, like so:
SELECT s.[Name] AS StoreName,
p.[LastName] + ', ' + p.[FirstName]
FROM [Sales].[Store] s
INNER LOOP 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 ;
Running this query results in the execution plan shown in Figure
18-14
.
