SQL SERVER OPTION (FAST N)
FAST
Specifies that the query is optimized for fast retrieval of the first number of rows.
After the first number of rows are returned, the query continues execution and produces its full result set.
Use Case: Improve scenarios where an Application Users wait for UI to load the full dataset and only to page through it.
The FAST hint instructs the Query Optimizer to use techniques like a Nested Join vs Merge or Hash Joins to return the first rows more quickly whereas the plan with the Nested Loops would NOT have been chosen since it was more expensive and runs longer due to cardinality for example.
The FAST hint typically only makes sense on complex queries where there exists numerous alternatives the Query Optimizer could choose from.
The FAST hint would cause the optimizer to pick a plan because it produces results faster.
As with any hints, they should be used rarely and monitor their performance on a frequency basis to look for any regression.
With this specific hint, you may see data quicker but the query duration could be longer vs no hint.
Example:
SELECT t1.*, t2.OrderQty
FROM [Sales].[SalesOrderHeader] t1
LEFT OUTER JOIN [Sales].[SalesOrderDetail] t2 ON t1.SalesOrderID = t2.SalesOrderID
ORDER BY t1.SalesOrderNumber DESC
OPTION (FAST 100);
|
|
|
|
|
|