How to speed up the query


  • I am doing a query on SQL Server that is taking so long because of the huge no. of records being fetched. I'm already using stored procedures. I'm trying to avoid using these:


    select * from table limit 1000




    select top 1000 from table


    The above methods require a trip to the sql server every time the next batch is fetched. Is there a way to do just one trip to the server but enable me to show some records while the rest are still being fetched? In other words, if I'm getting a million records, can I show a thousand to the user while waiting for the rest? When I do a query in SQL server management studio, it shows me some records immediately while the rest are still being fetched. Is there a way in C# (code behind) to get these first batch of records from sql server before it finish getting all the records? This way, the first batch of records can be shown in a Datagrid already and then later update the Datagrid when all the records are in.

    Thanks in advance.

    Monday, April 21, 2008 3:27 PM


  • Yes. Take a look at the OPTION (FAST N).  According to BOL:

    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.

    Here's a link.



    Wednesday, April 23, 2008 2:01 AM

All replies