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 (http://blog.sqlauthority.com)
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.
Thanks for witing on this topic, Looks like these can be used in certain scenarios like DISTINCT, would like to check this out.
[...] I wrote SQL SERVER – Challenge – Puzzle – Usage of FAST Hint and I did receive some good comments. Here is another question to tease your mind. Run following [...]
[...] SQL SERVER – Challenge – Puzzle – Usage of FAST Hint [...]
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:
http://bradsruminations.blogspot.com/2010/08/taking-hint.html
The FAST hint won’t help in just scanning one table… but it can help in JOINs.
–Brad
This is great blog post Sir!
I think you nailed it!
[...] SQL SERVER – Challenge – Puzzle – Usage of FAST Hint [...]
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
[...] The puzzle was posted here: SQL SERVER – Challenge – Puzzle – Usage of FAST Hint [...]
[...] SQL SERVER – Challenge – Puzzle – Usage of FAST Hint [...]
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.
[...] SQL SERVER – Challenge – Puzzle – Usage of FAST Hint (Solution) [...]
which query is ruuning faster?
1. Select * from tablename
Or
Select Column1,Column2,…. from tablename.