Databases Reference
In-Depth Information
The Query Optimizer usually accomplishes this
FAST
N
goal by avoiding any blocking
operators, like Sort, Hash Join or Hash Aggregation, so the client submitting the query
does not have to wait before the first records are produced. Let's see an example; run the
following query, which returns the plan shown in Figure 7-15.
SELECT
*
FROM
Sales
.
SalesOrderDetail
ORDER
BY
ProductID
Listing 7-25.
Figure 7-15:
Plan without a hint.
In this case, the Sort operator is the most effective way to get the records sorted by
ProductID
if you want to see the entire query output. However, since Sort is a blocking
operator, SQL Server will not produce any record until the sort is completed. Now,
supposing that your application wants to see a page with 20 records at a time, you can use
the
FAST
hint to get these 20 records as quickly as possible, as seen in the next query.
SELECT
*
FROM
Sales
.
SalesOrderDetail
ORDER
BY
ProductID
OPTION
(
FAST
20
)
Listing 7-26.
This time, the new plan, seen in Figure 7-16, scans an available non-clustered index while
performing Key Lookups to the clustered table. Since this plan uses random I/Os, it would
be very expensive for the entire query, but it will achieve the goal of returning the first 20
records very quickly.
