SQL SERVER – Challenge – Puzzle – Usage of FAST Hint

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)

About these ads

16 thoughts on “SQL SERVER – Challenge – Puzzle – Usage of FAST Hint

  1. 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

    Like

  2. 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.

    Like

  3. Pingback: SQL SERVER – Challenge – Puzzle – Why does RIGHT JOIN Exists Journey to SQL Authority with Pinal Dave

  4. Pingback: SQL SERVER – Challenge – Puzzle – Why does RIGHT JOIN Exists Journey to SQL Authority with Pinal Dave

  5. 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 ! :)

    Like

  6. Pingback: SQL SERVER – 3 Simple Puzzles – Need Your Suggestions « Journey to SQL Authority with Pinal Dave

  7. 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

    Like

  8. Pingback: SQL SERVER – Solution – Challenge – Puzzle – Usage of FAST Hint Journey to SQL Authority with Pinal Dave

  9. Pingback: SQL SERVER – Weekly Series – Memory Lane – #005 « SQL Server Journey with SQL Authority

  10. 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.

    Like

  11. Pingback: SQL SERVER – Weekly Series – Memory Lane – #006 « SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s