Professional Web Applications Themes

About resultset paging - Microsoft SQL / MS SQL Server

It is known to all of us that there isn't any one-step paging SQL statement in SQL Server. On the net, I found several solutions for resultset paging with TSQL. 1. Select ordered records into a temp table which has an indentity column (id), and then Select between id>a and id<=b from that temp table. Since a temp table is used, CONSIDERABLE IO OPERATIONS might occur when the resultset is huge. 2. Select with cursor. Since using cursor can Fetch only one row at a time, we might like to Insert what is fetched into a table variable and then ...

  1. #1

    Default About resultset paging

    It is known to all of us that there isn't any one-step
    paging SQL statement in SQL Server.

    On the net, I found several solutions for resultset
    paging with TSQL.

    1. Select ordered records into a temp table which has
    an indentity column (id), and then Select between
    id>a and id<=b from that temp table. Since a temp
    table is used, CONSIDERABLE IO OPERATIONS might
    occur when the resultset is huge.
    2. Select with cursor. Since using cursor can Fetch
    only one row at a time, we might like to Insert
    what is fetched into a table variable and then
    return the variable as one resultset. However, it
    is IMPOSSIBLE for us TO KNOW HOW MANY RESULTS are
    there in the database.
    3. Select all records and use ADO to filter the
    result. It is usually slow when the resultset is
    big thus it does need TIME AND MEMORY for so many
    data to travel between the database server and the
    ADO application.

    My condition is that i have a table with a million of
    records and a query might return 4% of them. The database
    server and the ASP.net application server is on the same
    machine. Someone kind enough to tell me which solution
    should i take at this occasion?




    w. Guest

  2. #2

    Default Re: About resultset paging

    I assume that your case a typical search application where the user enters
    some search criteria, and then you display the first page of the result, and
    the user can page forward in the resultset.

    In such applications, it is important to optimize for the most common
    scenario, which is to display the first (or perhaps the second or third)
    page of a potentially enormous result set.

    A very simple and extremely effective solution is this:

    Assume you want to display the second page. You want record 21-40 of a
    potentially very big recordset.

    * Use TOP or ROWCOUNT to limit the number of rows returned to 40.
    * Use a datareader, and just skip the first 20 rows (Alternatively you can
    read all 40 rows into a dataset)
    * Display rows 21-40 for the user
    * To find out how many rows you have in total, you issue a separate SELECT
    COUNT(*) with the same WHERE-clause as in the original query

    This solution is MUCH more effective than any of the solutions you outlined.
    The most costly part is often performing the COUNT(*)

    /SG

    "w. jORDAN" <com> wrote in message
    news:phx.gbl... 


    Stefan Guest

  3. #3

    Default Re: About resultset paging

    Hello Stefan Gustafsson,

    Thank you very much for your suggestion.
    Hmm, i missed what you pointed out.

    It seems that it is effective when the users only want the first several
    pages.
    --Hmm, in most cases, they do.
    And i can cache the page count (the total number of the resultset)
    with ViewState or something between sessions. So the Select Count(*)
    will only be executed only once when the users post a query.
    And it seems that i should also try some other paging methods when the
    users require to see pages at the back.

    After all, your guide does help.
    Thank U!
    Jordan

    "Stefan Gustafsson" <se> Wrote
    news:eUGC$phx.gbl... 
    and 
    outlined. 


    w. Guest

  4. #4

    Default Re: About resultset paging

    Perhaps this has some drawbacks I don't see, but I've been using queries
    like the one below, which only returns a small set of records over the
    network. The example below assumes that you want rows 80-100 from Table1.

    SELECT Top 20 * FROM Table1 WHERE [UniqueKey] IN
    (SELECT Top 100 [UniqueKey] FROM Table 1 ORDER BY QueryField DESC)
    ORDER BY QueryField ASC

    - Steve Harclerode

    "w. jORDAN" <com> wrote in message
    news:%phx.gbl... 


    Steve Guest

  5. #5

    Default Re: About resultset paging

    Oh yes, there are drawbacks:

    1) The query does not work as written - you need to swap DESC with ASC. This
    will make the query return the data in reverse order. This could be dealt
    with on the client, but it reuires extra work.
    2) Imagine that instead of just querying a table, you need to execute a
    fairly complex select with a complex WHERE condition. Your query would
    include the complex query two times, which would make the syntax very
    complex.
    3) If you run your query on a very large table, it is quite inefficient. The
    server implements the IN operation using a hash join with a full table scan
    on one side. When I tried it on a table with 100000 records, it took several
    seconds to return the first page. My solution returns instantly in that
    situation.
    4) The performance might get even worse if the base query is complex (since
    you are in effect executing the same query twice)
    5) Dynamic SQL is required to handle the dynamic TOP parameters - this can
    cause both security and performance problems.

    In short: This query might look elegant, but I recommend my solution as long
    as the network bandwidth between the webserver and the database is not a
    narrow sector (it seldom is)

    /SG

    "Steve Harclerode" <com> wrote in message
    news:phx.gbl... [/ref]
     
    >
    >[/ref]


    Stefan Guest

  6. #6

    Default Re: About resultset paging

    Dyslexia strikes again. I couldn't find a copy of the actual query I used
    before, and thought I could reproduce it. Here's the real deal:

    SELECT * FROM FORMATS WHERE [ID] IN
    (SELECT TOP 20 [ID] FROM formats WHERE [ID] IN
    ( SELECT TOP 100 [ID] from formats ORDER BY [ID] ASC )
    ORDER BY [ID] DESC)
    ORDER BY [ID] ASC

    And of course Stefan is right that it has performance issues using large
    recordsets. The recordsets in my DB are always smaller than 100,000 records,
    and a lot of the consumers of my app only have 56K (or less) bandwidth.

    If I had recordsets as big as yours but still had *my* bandwidth issues, I'd
    probably write a stored proc that used a #temp table

    - Steve Harclerode


    Steve Guest

  7. #7

    Default Re: About resultset paging

    Hello Stefan and Steve,

    I agree with Stefan.

    "In" operation is actually less efficient on large tables.
    My company is going to add their 100,000 records into the table,
    so i am very much worried about the performance when some
    complex queries are performed against that table.

    When used with web applications, the "TOP n" can not
    be set by procedure paramters, then "Dynamic SQL" have to
    be used.

    If the user does want to see results at the middle, or at the
    back of the resultset, which solution might you prefer?
    Using a cursor to retrieve from results one by one,
    or a table variable (or temp table) to store the mid result?

    Your experience and kind helps are, and will be, very much appreciated.


    "Steve Harclerode" <com> wrote in message
    news:OMGK4o%phx.gbl... 
    records, 
    I'd 


    w. Guest

  8. #8

    Default Re: About resultset paging

    Hello Stefan,
     
    forward 

    Maybe you are right.
    My development schedule is really tight.
    Tweaking should be done after the system is finished.

    And maybe i should try to combine those two techiniques in the
    search procedure, since i have ever experienced that users
    frequently look up the records at the back in a firewall log
    ysis application--when one user hit the last page among
    100,000 records, the system refused other requests absolutely.

    Thus, i might try the following mechanism,

    If (begining record number < a certain number, maybe 500) then
    use your technique
    Else
    use table variable or temp table to store the mid-term result set
    fetch the desired ones
    End

    Since the so-called limiter, the "Certain Number", can be tweaked,
    and an If statement won't take too much execution time,
    I can test the performance at practical run time afterwards.

    Before the system is complete, i should LISTEN TO YOU and take your
    technique at first--to save DEVELOPMENT TIME.

    This post might be an end of this thread, and the beginning of
    my development.

    Thank you for your opinions and quick replies!!
    They are very much appreciated!

    Best regards,
    Jordan

    "Stefan Gustafsson" <se> wrote in message
    news:phx.gbl... 
    forward 
    temporary 
    the 


    w. Guest

Similar Threads

  1. Can not open resultset
    By Rob in forum MySQL
    Replies: 2
    Last Post: May 21st, 07:49 PM
  2. Resultset Comparison
    By alagukannan in forum Coldfusion Database Access
    Replies: 1
    Last Post: December 8th, 07:59 PM
  3. Export resultset
    By Dpcma in forum PHP Development
    Replies: 2
    Last Post: September 29th, 02:15 PM
  4. ResultSet is not updatable.
    By xixi in forum IBM DB2
    Replies: 7
    Last Post: August 7th, 10:53 PM
  5. resultset.close()
    By xixi in forum IBM DB2
    Replies: 3
    Last Post: July 14th, 05:04 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139