I was recently working with various SQL Server Hints. After working for a day on various hints, I realize that for one hint, I am not able to come up with good example. The hint is FAST.
Let us look at the definition of the FAST hint from the Book On-Line.
- FAST number_rows
- 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.
Now the question is in what condition this hint can be useful. I have tried so many different combination, I have found this hint does not make much performance difference, infect I did not notice any change in time taken to load the resultset. I noticed that this hint does not change number of the page read to return result. Now when there is difference in performance is expected because if you read the what FAST hint does is that it only returns first few results FAST – which does not mean there will be difference in performance.
I also understand that this hint gives the guidance/suggestions/hint to query optimizer that there are only 100 rows are in expected resultset. This tricking the optimizer to think there are only 100 rows and which (may) lead to render different execution plan than the one which it would have taken in normal case (without hint). Again, not necessarily, this will happen always.
Now if you read above discussion, you will find that basic understanding of the hint is very clear to me but I still feel that I am missing something. Here are my questions:
1) In what condition this hint can be useful? What is the case, when someone want to see first few rows early because my experience suggests that when first few rows are rendered remaining rows are rendered as well.
2) Is there any way application can retrieve the fast fetched rows from SQL Server?
3) Do you use this hint in your application? Why? When? and How?
Here are few examples I have attempted during the my experiment and found there is no difference in execution plan except its estimated number of rows are different leading optimizer think that the cost is less but in reality that is not the case.
USE AdventureWorks
GO
SET STATISTICS IO ON
SET STATISTICS TIME ON
GO
---------------------------------------------
-- Table Scan with Fast Hint
SELECT *
FROM Sales.SalesOrderDetail
GO
SELECT *
FROM Sales.SalesOrderDetail OPTION (FAST 100)
GO
---------------------------------------------
-- Table Scan with Where on Index Key
SELECT *
FROM Sales.SalesOrderDetail
WHERE OrderQty = 14
GO
SELECT *
FROM Sales.SalesOrderDetail
WHERE OrderQty = 14
OPTION (FAST 100)
GO
---------------------------------------------
-- Table Scan with Where on Index Key
SELECT *
FROM Sales.SalesOrderDetail
WHERE SalesOrderDetailID < 1000
GO
SELECT *
FROM Sales.SalesOrderDetail
WHERE SalesOrderDetailID < 1000
OPTION (FAST 100)
GO
Reference: Pinal Dave (https://blog.sqlauthority.com)
16 Comments. Leave new
Something I wrote for Chapter 7 of my book about the Query Optimizer
“This hint can help in applications where you want to retrieve the first records of a query as quickly as possible and perhaps you will not be using the remaining records of the query at all. The price to pay for achieving this goal is that retrieving the remaining records of the query may take longer than a plan without this hint. The Query Optimizer usually accomplishes this goal by avoiding any blocking operators, like sort, hash join or hash aggregation, so the client submitting the query does not have to wait before producing the first records. Since the query is optimized to retrieve the first N records as soon as possible, retrieving all the records of the query may be very expensive.”
Ben
We have many complex queries, most of them returns DISTINCT rows. These query are slow because of DISTINCT. When ever we need to get some quick results we like to use FAST hint. Which omits sort process for distinct values and returns results quickly.
but you may get duplicate rows instead which defeats the logic if as u said distinct is not applied for the first fast (n) rows.
Thanks for witing on this topic, Looks like these can be used in certain scenarios like DISTINCT, would like to check this out.
Hello Pinal,
Thank You for writting this topic! It helped me to figure out how to accelerate my view. It uses couple unions, and one pivot, and the difference is really very important!
I really appreciate Your work. Keep it simple ! :)
Hi Pinal…
I wrote a blog post in August that gave an example of using the FAST hint:
The FAST hint won’t help in just scanning one table… but it can help in JOINs.
–Brad
I never use this hint
but I think it maybe usefull for some reporters which not showing data until recieve it all.
if programmer can trick the reporter and with this syntax show preview of first page of report, it will be usefull for many situations
I’ve seen it used in in situations where the query is feeding an SSIS package. For big record sets this can make a difference because SSIS can start to process the returned records sooner.
which query is ruuning faster?
1. Select * from tablename
Or
Select Column1,Column2,…. from tablename.
doesn’t really matter about complexity if u select all, but for code maintenance, the 2nd give u no headache
The fast option is used by programmers to speed up presentation and movement through a scrolling list.
I have a situation where I submit a query using FAST 1 but alter the value in the WHERE clause with markedly different results. In scenario A the response is sub-second, in scenario B the response is seven minutes. There is a non-clustered in on the WHERE column. Query plans are identical except scenario B shows bad (very high) row estimates vs actual rows. Challenging to explain to say the least. The only thing I can think of is histogram step locationf for that index’s values. The two values fall in different histogram steps, A is in step 3, B is in step 72.
I believe, SQL is making more efforts to get you all the rows but getting 1 row faster due to Fast 1 hint.