SQL Server Performance

How to use OPTION (FAST n) ?

Discussion in 'Performance Tuning for DBAs' started by kamleshkumar, Apr 17, 2007.

  1. kamleshkumar New Member

    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


  2. FrankKalis Moderator

    Can't you filter the rows by a WHERE clause?

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  3. Adriaan New Member

    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.
  4. kamleshkumar New Member

    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..
  5. Adriaan New Member

    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.
  6. kamleshkumar New Member

    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..
  7. kamleshkumar New Member

    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...
  8. Roji. P. Thomas New Member

    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
  9. MohammedU New Member

  10. kamleshkumar New Member

    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
  11. Adriaan New Member

    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.
  12. kamleshkumar New Member

    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
  13. Adriaan New Member

    You're sure you're not looking for a paging solution like for a web page?
  14. kamleshkumar New Member

    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
  15. kamleshkumar New Member

    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...
  16. Adriaan New Member

    Have you looked into optimizing the processing that makes the query take such a long time?
  17. kamleshkumar New Member

    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..
  18. kamleshkumar New Member

    SomeOne Plz help????

    Thank You,
    Kamlesh Kumar
  19. Adriaan New Member

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

    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
  21. MohammedU New Member

    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.
  22. kamleshkumar New Member

    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
  23. MohammedU New Member

    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.
  24. kamleshkumar New Member

    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

Share This Page