What does OPTION FAST in SELECT statement do?

Question

I have done some digging on what the OPTION (FAST XXX) query hint does inside a SELECT statement and I'm still confused about it. According to MSDN:

Specifies that the query is optimized for fast retrieval of the first number_rows. This is a nonnegative integer. After the first number_rows are returned, the query continues execution and produces its full result set.

For me that does not make much sense but basically the query can get the first XXX rows really fast then the rest at normal speed?

The Microsoft Dynamics query that got me thinking on this is:

select pjproj.project,pjproj.project_desc,pjproj.customer,pjproj.cpnyid
from pjproj WITH (NOLOCK)
where project like  '%'
order by project OPTION(FAST 500)

Can anyone explain exactly what this query hint is doing and its advantage over not using it?

Answer 1

A FAST N will tell SQL Server to generate an execution plan with quickly returning the number of rows defined as N.

Note that the estimates will be as per the N since you are telling sql server to retrieve N rows as fast as possible.

e.g. running below query with fast 500:

-- total rows : 19972
 SELECT [BusinessEntityID]
      ,[TotalPurchaseYTD]
      ,[DateFirstPurchase]
      ,[BirthDate]
      ,[MaritalStatus]
      ,[YearlyIncome]
      ,[Gender]
      ,[TotalChildren]
      ,[NumberChildrenAtHome]
      ,[Education]
      ,[Occupation]
      ,[HomeOwnerFlag]
      ,[NumberCarsOwned]
  FROM [AdventureWorks2012].[Sales].[vPersonDemographics]
  order by BusinessEntityID
  option (fast 500)

Est vs Actual rows with option (fast 500)

Est vs Actual rows without option (fast 500)

A use case would be when an application is doing caching (load large amount of data in background) and wants to show the user a slice of data as quickly as possible.

Another interesting use case is in SSIS land that Rob Farley describes using FAST N as a catalyst speeding up the data retrieval.

By adding this hint, it felt like a magic wand had been waved across the query, to make it run several times faster.

See Remus Rusanu's answer as well.

Answer 2

When you use this query hint OPTION(FAST n) the optimizer picks an execution plan that can return that amount of records in the fastest way possible. Normally, the optimizer tries to decide on a path that can efficiently return the complete resultset. So, if you want a set of rows to come back quickly, you could possibly use this to retrieve them, but in most cases, using this hint will cause the complete result set to come back slower versus letting the optimizer use a plan to do so.

Just to elaborate on a use case: you may have two fairly large tables that you need to join together, but you know there's a only smaller set of data that you need to work with out of one of them. In this case, using FAST over a larger table without having to create additional indexes/overhead could help instead of lettings SQL create a giant hash table in the background.

Answer 3

I stumbled upon the same question a couple of years ago when investigating performance issues in Dynamics AX. Following Microsoft's explanation, I could see a different execution plan triggered by the hint and this corresponds to the other answers given here.

With the hint, SQL Server tries to find an execution plan that allows a sort of streaming of results, as they come out of a nested loop for instance, instead of an execution plan that requires all records to be read (and potentially sorted) before being output.

The only issue in my case is that SQL server always took (numerous) minutes to return the results when using the hint, whereas the query returned results (the whole result set) in a matter of seconds without the hint... not quite what was expected. I'd personnally use this hint with great caution, and not systematically on all forms like in our Dynamics environment (well, not anymore).

So, to answer the OP: like many hints, it doesn't necessarily help improving the query like it is supposed to do (so... test, test, test!)