Query Design Analysis - SQL Server Query Performance Tuning - page 362

Figure 18-14. More changes from using the LOOP join hint

As you can see, there are now four tables referenced in the query plan. There have been four tables referenced
through all the previous executions, but the optimizer was able to eliminate one table from the query through the
simplification process of optimization (referred to in Chapter 8). Now the hint has forced the optimizer to make
different choices than it otherwise might have and removed simplification from the process. The reads and execution
time suffered as well.

Table 'Person'. Scan count 0, logical reads 2155
Table 'Worktable'. Scan count 0, logical reads 0
Table 'Employee'. Scan count 1, logical reads 9
Table 'SalesPerson'. Scan count 0, logical reads 1402
Table 'Store'. Scan count 1, logical reads 103
CPU time = 0 ms, elapsed time = 92 ms.

JOIN hints force the optimizer to ignore its own optimization strategy and use instead the strategy specified by the
query. JOIN hints generally hurt query performance because of the following factors:

• Hints prevent autoparameterization.

•
Therefore, it makes sense to not use the JOIN hint but to instead let the optimizer dynamically determine a
cost-effective processing strategy. There are exceptions of course, but the exceptions must be validated through
thorough testing.

The optimizer is prevented from dynamically deciding the joining order of the tables.

INDEX Hints
As mentioned earlier, using an arithmetic operator on a WHERE clause column prevents the optimizer from choosing
the index on the column. To improve performance, you can rewrite the query without using the arithmetic operator
on the WHERE clause, as shown in the corresponding example. Alternatively, you may even think of forcing the
optimizer to use the index on the column with an INDEX hint (a type of optimizer hint). However, most of the time, it is
better to avoid the INDEX hint and let the optimizer behave dynamically.
To understand the effect of an INDEX hint on query performance, consider the example presented in the “Avoid
Arithmetic Operators on the WHERE Clause Column” section. The multiplication operator on the PurchaseOrderID