Why use Query Hint?
Tradeoffs are at the heart of any query suggestion; often, you are sacrificing something, like flexibility, in order to increase consistency or a very particular performance attribute. When delivering code results, for instance, you might want to view the data right away, even before the entire set of data has been delivered. You may return a subset of rows to look at PRIOR to returning the whole result set, did you know that?
The optimizer can fast return a certain number of rows by using the FAST n query suggestion. Consider a result screen for an application where users regularly wait for data to arrive. Wouldn’t it please those users if you were able to return rows more quickly? For instance, using the FAST 75 table hint will provide the first 75 results while attempting to return the remaining rows. Users are able to operate with the data before the rest of the screen has loaded with it because of this.
USE AdventureWorksDW2019 GO SET STATISTICS TIME, IO ON GO SELECT [OrderDate],[UnitPrice],[OrderQuantity] FROM [dbo].[FactResellerSalesXL] ORDER BY OrderDate GO
Be aware that it is still running after 30 seconds and that the result grid contains no information. Let’s end it, then.
The fast hint instructs the query optimizer to speed up the return of the initial rows by switching from hash joins to methods like nested join. Always keep in mind that query hints, including this one, can have a detrimental impact on the query’s overall performance, so you should test thoroughly before using any of them.
Example :
Let’s try the same operation now using the Query Hint.
SELECT [OrderDate],[UnitPrice],[OrderQuantity] FROM [dbo].[FactResellerSalesXL] ORDER BY OrderDate OPTION ( FAST 75);
If you switch to the results tab, the first 75 rows are displayed right away and the page starts to populate. Although it was immediate, I was unable to quickly screen capture it because it listed 75 rows.
You can see the potential effects on users. This is a wonderful option if your application requires users to see data as soon as feasible. Try it out and discover how it can benefit you.
