SQL Server – What Does OPTION FAST in SELECT Statement Do?

hintsmicrosoft-dynamicssql servert-sql

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?

Best Answer

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.

Related Solutions

Sql-server – Why does OBJECTPROPERTY return NULL

OBJECTPROPERTY is local to the database the query is run it. So the object_id passed in is resolved against master.sys.objects: but the object_id comes from msdb

So here you have case 2.

On my server, I have 37 matching object_id values between msdb and SomeDBOnMyServer. But the names are different.

USE SomeDBOnMyServer
GO
SELECT
  object_id,
  name,
  OBJECT_NAME(object_id)
FROM msdb.sys.objects
WHERE OBJECT_NAME(object_id) <> name

Obviously, I have a lot of rows where OBJECT_NAME(object_id) is NULL that are filtered here

Sql-server – How does OPTION (FAST 1) actually interact with a client

If it were a query that returned more than 1 row I'd speculate that someone at the vendor (way back when, given the version of SQL Server) stumbled on the query optimiser producing a preferable plan when FAST was specified as a hint.

As it's returning just 1 row, the explanation probably has more in common with the infinite monkey theorem than reasoned judgement. A junior saw a hint for FAST and decided that was preferable to his query not being fast.

This makes me wonder if the client has somehow broken communication, would the server keep the transaction open, processing the full result set after the first n rows are returned and leave the transaction open?

The query returns 1 row so there is no further result set to process. I'm inclined to suspect there is other code further up the stack that's causing the problem.

Best Answer

Related Solutions

Sql-server – Why does OBJECTPROPERTY return NULL

Sql-server – How does OPTION (FAST 1) actually interact with a client

Related Question