Returning Only Those Records on Page N


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: Returning Only Those Records on Page N

  1. #1
    Randy Pearson Guest

    Returning Only Those Records on Page N


    I am constructing a web-based application that displays search results one
    page (say 20 records) at a time. The data resides in SQL Server 7.0.

    Rather than returning all records meeting the query conditions to the application
    tier, I would like to return only those for a specific page. For example,
    if I pass in a page number parameter of 5, I would get back just records
    81-100. I realize I could specify TOP 100 and then discard the first 80 in
    my application tier, but I don't want that performance hit when the page
    number gets higher.

    Is there an efficient, scalable approach for doing this in a stored procedure
    using T-SQL?

  2. #2
    Ted McNeal Guest

    Re: Returning Only Those Records on Page N


    "Randy Pearson" <randyp@cycla.com> wrote:
    >
    >I am constructing a web-based application that displays search results one
    >page (say 20 records) at a time. The data resides in SQL Server 7.0.
    >
    >Rather than returning all records meeting the query conditions to the application
    >tier, I would like to return only those for a specific page. For example,
    >if I pass in a page number parameter of 5, I would get back just records
    >81-100. I realize I could specify TOP 100 and then discard the first 80

    in
    >my application tier, but I don't want that performance hit when the page
    >number gets higher.
    >
    >Is there an efficient, scalable approach for doing this in a stored procedure
    >using T-SQL?



    Randy,

    ADO has a paging function for recordsets that will help you manage this.


    You can select the page size for a recordset to define the number of records
    that constitute a page. This is not to be confused the pages of data w/
    regards to data storage in your db.

    You will start by defining the page size for the return
    rst.PageSize = 20
    You can think of this as dividing your recordset into blocks.
    You can set the absolute page to determine which block to render to the client.
    rst.AbsolutePage = 2
    This will allow you render records 21-40.

    You should set the cache size to the same as the same as the page size to
    prevent multiple trips to the server.
    Also, it makes sense to open a cursored recordset. If you open a firehose
    cursor (noncursored recordset) then all of the records will be dumped to
    the client upon opening the recordset. To ensure that you limit the records
    transferred to your client based upon your pagesize and absolutepage settings
    then you will want to use a cursor.

    Ted McNeal

  3. #3
    Randy Pearson Guest

    Re: Returning Only Those Records on Page N


    Ted,

    >ADO has a paging function for recordsets that will help you manage this.

    You can select the page size for a recordset to define the number of records
    that constitute a page.
    >
    >You will start by defining the page size for the return
    >rst.PageSize = 20
    >You can set the absolute page to determine which block to render to the

    client.
    >rst.AbsolutePage = 2
    >This will allow you render records 21-40.
    >
    >You should set the cache size to the same as the same as the page size to
    >prevent multiple trips to the server.


    I've tried your approach and see how it would work. It looks like you are
    restricted to client-side cursors (adUseClient), since properties like AbsolutePage
    aren't suported when I use adUseServer on SQL Server 7. Also, since you can't
    set AbsolutePage until the recordset is open, you always get Page 1 before
    you can tell it that's not what you want. So I left CacheSize at 1 when opening,
    then bumped it up to my desired PageSize and requested the actual AbsolutePage
    I wanted. Seems to work fine.

    >Also, it makes sense to open a cursored recordset. If you open a firehose
    >cursor (noncursored recordset) then all of the records will be dumped to
    >the client upon opening the recordset. To ensure that you limit the records
    >transferred to your client based upon your pagesize and absolutepage settings
    >then you will want to use a cursor.


    This looks like the better approach. A little more coding, but way better
    scalability I would think. I got a stored procedure working that I can pass
    page number and page size to (in addition to query parameters).

    Regards and thanks,

    -- Randy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center