none
How to speed up the query

    Question

  • 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

     

    or

     

    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

Answers

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

    http://msdn2.microsoft.com/en-us/library/ms181714.aspx

    HTH...

    Joe

    Wednesday, April 23, 2008 2:01 AM
    Moderator

All replies