How to use OPTION (FAST n) ?

Hi All,

I'm trying to use "OPTION (FAST n)" where n = 10 in my case...
The query is
select * from table_name
this takes 30 secs
and when I use
select * from table_name OPTION (FAST 10)
here too it takes 30 secs

I dont know if some settings are to be done before running this query..
I have gone through many documents.. Everywhere they say OPTION(FAST n) helps to retrive
first n rows as soon as possible.. but in my case its not the same..
So plz help me!!

Thanks in advance for any replies..

Best Regards,
kamlesh kumar

OPTION (FAST n) does not make the whole query run any faster: it returns n rows to the client as soon as they have been found, then continues with the remaining rows.

In the end, the whole resultset will take roughly the same time as without the FAST option.

I think i should apologise for this.. Actually my query has a WHERE clause..
I would like to know how to use OPTION(FAST n) in addition to WHERE clause..

The OPTION clause is always the last part of the query, so it comes after any WHERE, GROUP BY, HAVING or ORDER BY clause in your query.

I want the first "n" rows to be available as soon as possible.. Its OK for me if the remaining rows take even a little more time..

I used the query
select * from table_name OPTION (FAST 10)
it has a WHERE clause too..

This is the query i'm using
select * from table_name where journal_id > 10000 OPTION (FAST 10)
the first 10 rows of the result set are not returned immediately..
whether i use OPTION(FAST 10) or not, in any case it takes 30 secs and returns the whole result set (total records in the table) at once...

With the OPTION Fast N, The query is optimized to return the first n records immediately. But this depends on the plan SQL server chooses for the query and the available indexes. You can check the execution plan. If they are exactly same, then you will not find any performance difference.

Roji. P. Thomas
SQL Server MVP
http://toponewithties.blogspot.com

Check the following before moving forward...

http://blogs.msdn.com/queryoptteam/archive/2006/03/30/564912.aspx
http://www.sql-server-performance.com/query_execution_plan_analysis.asp
http://www.sql-server-performance.com/transact_sql.asp


MohammedU.
Moderator
SQL-Server-Performance.com

All postings are provided “AS IS” with no warranties for accuracy.

Thank you Experts for all your support..
But till now i'm not clear about whether OPTION(FAST n) really works or not..
So if possible can anyone plz show me with an example, so that i can try it out, to make sure OPTION(FAST n) really works..

Thanks In Advance for any replies
kamlesh kumar

I tried running queries with the FAST option from Query Analyzer, both for a local instance of SQL Server and an instance on a network server. Whatever number of rows I gave, it looked like the results were just getting returned as normal, without really having a group of fast records. Without appropriate indexes, I don't think FAST has any positive effect.

You might get different results if you issue the query from a different client than QA, like Access.

Thank you Experts for all your support..
But till now i'm not clear about whether OPTION(FAST n) really works or not..
So if possible can anyone plz show me with an example, so that i can try it out, to make sure OPTION(FAST n) really works..

Thanks In Advance for any replies
kamlesh kumar

You're sure you're not looking for a paging solution like for a web page?

Thank you for your reply Sir..

I found the following queries at
http://blogs.msdn.com/queryoptteam/archive/2006/03/30/564912.aspx

I tried with these queries but instead of @i starting from 0 and going till 5000 i made it to go till 500000

create table A(col1 int, col2 binary(100), col3 int)
declare @i int
set @i = 0
while @i < 5000
begin
insert into A(col1, col2, col3) values (@i, 0x3333, rand()*1000)
set @i = @i + 1
end


create clustered index i1 on A(col1)



-- should pick a series of hash joins

select A1.* from
A as A1 inner join A as A2 on A1.col1 = A2.col1
inner join A as A3 on A1.col1 = A3.col1



-- if there is a row goal, we#%92ll pick the loop join plan that returns one (or a few) row(s) quickly

select A1.* from
A as A1 inner join A as A2 on A1.col1 = A2.col1
inner join A as A3 on A1.col1 = A3.col1
option (fast 1)



I tried this in SQL 2000 query analyzer it takes approx 1 min for both the cases(with OPTION / without OPTION), So is there any case where OPTION(FAST n) is useful.. if so
plz tell..
Or else plz tell me an alternative to OPTION(FAST n)..
TOP(n) is an alternative but it does only for 'n' records.. but it will be great if i can get a way where it returns me 'n' records as soon as possible, but query execution continues in the background till it gets all the record

Thank You,
Kamlesh kumar

You're sure you're not looking for a paging solution like for a web page?

I didn't get what U actually meant by that?

But my need is such that i have to return the records which matched a specific "search term(s)".... But getting all the records and then do some processing and then displaying the output takes a long time..(Or probably i dont want to waste time in waiting for all the records which are not at all required at the start itself, they can come slowly)..

So if i can get some records(sasy 10) as early as possible and show them in the first page, and then by the time first page is viewed other records can be collected...

Have you looked into optimizing the processing that makes the query take such a long time?

Those are not my queries, I got them from the below mentioned link
http://blogs.msdn.com/queryoptteam/archive/2006/03/30/564912.aspx

I have written to you about this in one of my previous reply..
I got CREATE TABLE, INSERT, CREATE INDEX, and finally the SELECT (without OPTION and with OPTION) queries all from the same link..

As U have said in one of the previous reply that without proper INDEXing FAST will not have any effect, here in above link they have created an INDEX on col1 of table A..

create clustered index i1 on A(col1)

I dont know what better optimization could be done to the queries in link..
I have mentioned about why I need this OPTION(FAST n) to work..
As its always better i can avoid waiting the time needed to process entire recordset.. even if its 1 or 2 sec.. because for me to start my work its good enough if i get initial few records..

Even If time to get initial 'n' records is 1 sec and
for entire records is 10 sec..
I can atleast start my work (10 - 1) = 9 sec earlier..hence save 9 sec..

Thank You,
Kamlesh Kumar..

SomeOne Plz help????

Thank You,
Kamlesh Kumar

Did you check the query plan? If you see table scans, and no index seeks or index scans, you are missing appropriate indexes.

I have checked plans

for Graphical Query Execution Plan
1. without OPTION(FAST 10) : Number of records read which is pointed by the arrow is = Total Number of records returned for query
2. with OPTION(FAST 10) : Number of records read is again = Total Number of records returned for query

for Graphical ESTIMATED Query Execution Plan
1. without OPTION(FAST 10) : Number of records read = Total Number of records returned for query
2. with OPTION(FAST 10) : Number of records read is = 10

Is there anyway by which i can force the optimizer to follow the Estimated Query Execution Pan ONLY

Thank You,
kamlesh kumar

I don't think you force estimated query plan but you can force the indexes...


MohammedU.
Moderator
SQL-Server-Performance.com

All postings are provided “AS IS” with no warranties for accuracy.

Thank You...
Has OPTION(FAST n) worked for anyone ???????
If YES plz tell me how ???? Plz send me an example to try out..
If NO why is it present in SQL SERVER.....
I want an example which can demonstrate me that OPTION(FAST n) really works....

Thank You,
Kamlesh Kumar

It depends on the data, you may not see the difference when it comes to displyaing because it almost the as fast as regular one...
Some times using FAST option uses more resources and cuase more I/O...

check the following example and see STATS I/O results...it works in different way..

USE NORTHWIND
GO
SET STATISTICS IO ON
GO
SET STATISTICS TIME ON
GO
select *
from orders o inner join [order details] od on o.orderid=od.orderid
order by o.orderid
option(fast 10)

go

select *
from orders o inner join [order details] od on o.orderid=od.orderid
order by o.orderid
GO



MohammedU.
Moderator
SQL-Server-Performance.com

All postings are provided “AS IS” with no warranties for accuracy.

Thanks To All Moderators and everyone else who took sometime to reply me back.. I think with this, I now know better about OPTION(FAST n) than I earlier knew..


Thank You All,
Best Regards,
Kamlesh Kumar