What is the OPTION (FAST N) Query Hint?

How familiar are you with the OPTION (FAST N) query hint? It’s not one that I had ever used, so I decided to experiment with it a bit. Let’s look at what it does and how to apply it to a query.

What is OPTION (FAST N)

When using the OPTION (FAST N) query hint, SQL Server attempts to focus on getting N number of rows returned. For example, let’s say I’m running a query that’s going to return hundreds of rows. I might want to see the first 50 or so as fast as possible, so I can begin reviewing those while the query finishes returning the remaining rows.

Attempts at a Good Example

I tried playing around with some queries against the StackOverflow2013 database. I’m pulling back a list of all users from the Users table and ordering by CreationDate:

SELECT DisplayName, Reputation, CreationDate
FROM Users
ORDER BY CreationDate;
GO

This doesn’t lead to much of a wait for me. After 2 seconds or so, I see results showing up.

Still, I’ll try to use OPTION (FAST N) and see 50 rows as fast as possible. I can add OPTION (FAST 50) to my query and run:

SELECT DisplayName, Reputation, CreationDate
FROM Users
ORDER BY CreationDate
OPTION (FAST 50);
GO

With the query hint, I see results immediately.

2 seconds seems small, but it can have a huge impact in cases where a query is being constantly executed many times a minute. Is 2 seconds a big deal in my example? I don’t think so. But after running various queries, this is the closest I could get.

No Patience

If you need to see a subset of rows right away, OPTION (FAST N) might be your answer. Maybe this is something that you never knew you needed, and it will make an impact. Like many query hints, adding it might help or it might make things worse, so if you do use it, make sure to thoroughly test.

Thanks for reading!