Query hints are always about tradeoffs—typically you are giving up something, such as flexibility, to achieve more consistency, or a very specific performance characteristic. One example of this might when returning code results, that you would want to see the data as soon as possible, even before the complete set of data has been returned. Did you know that you can return a subset of rows to look at BEFORE the entire result set is returned?
The FAST n query hint allows the optimizer to return a specified number of rows as quickly as possible. Imagine an application result screen where users frequently wait for data to appear. Wouldn’t it make those users happy if you could return rows faster? Using the FAST 75 table hint for example will return the first 75 records of the results set while still working to return the remaining rows. This allows users to start working with the data before the rest of the screen loads with data.
Let’s see it in action!
- USE AdventureWorksDW2016CTP3
- GO
- SET STATISTICS TIME, IO ON
- GO
- SELECT [OrderDate],[UnitPrice],[OrderQuantity]
- FROM [dbo].[FactResellerSalesXL]
- ORDER BY OrderDate
- GO
Note after 30 seconds it is still running and I have no results in the result grid. So, let’s kill it.
The fast hint works by telling the query optimizer to use techniques like nested join in lieu of hash joins to return the first rows more quickly. Always keep in mind that query hints can negatively impact the overall performance of the query, and you should test extensively before using any query hints including this one.
Now let’s run the same thing with the Query Hint.
- SELECT [OrderDate],[UnitPrice],[OrderQuantity]
- FROM [dbo].[FactResellerSalesXL]
- ORDER BY OrderDate
- OPTION ( FAST 75);
If you pop over to the results tab you immediately see the first 75 rows and it begins to populate right away. It was actually immediate but I couldn't screen capture fast enough and it listed 75 rows ;)
You can imagine what impact this can have on users. If you have an application in which users need to start seeing data as soon as possible, this is a great option. Play around with it and see how it can help you.