The FAST number_rows Query Hint

Published On: 2019-10-16By:

Query hints are always about tradeoffs—typically you are giving up something, such as flexibility, to achieve more consistency, or very specific performance characteristic. One example of this migt 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 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 for 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.


Contact the Author | Contact DCAC

Video

Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.

Awards & Certifications

Microsoft Partner    Microsoft MVP    Microsoft Certified Master    VMWare Partner    VMWare vExpert
   Best Full-Service Cloud Technology Consulting Company    Insights Sccess Award    Technology Headlines Award    Golden Bridge Gold Award    CIO Review Top 20 Azure Solutions Providers
Share via
Copy link