Professional Web Applications Themes

Large recordsets ... - Microsoft SQL / MS SQL Server

This is more of a design / implementation question. I'm writting some code that needs to implement a number of interfaces that can be called by an external application to popualte a recordset and scroll through it. - A guess a bit like how we use the ADO api. The recordsets can containe between 0 and 10s of thousands of records. Given some query information I need to be able to do the following 1) Return the TOTAL number of records 2) Return 100 rows at a time to the calling application 3) If the query is ordered by say ...

  1. #1

    Default Large recordsets ...

    This is more of a design / implementation question.

    I'm writting some code that needs to implement a number of interfaces that
    can be called by an external application to popualte a recordset and scroll
    through it. - A guess a bit like how we use the ADO api.

    The recordsets can containe between 0 and 10s of thousands of records.

    Given some query information I need to be able to do the following

    1) Return the TOTAL number of records
    2) Return 100 rows at a time to the calling application
    3) If the query is ordered by say LastName, then when the user presses a
    letter it will automatically pull back 100 rows starting with that letter.

    Obviously I can not cache the entire recordset - then it would be easy.
    The bit of this that I'm having difficulties getting my head around is

    1) How can a implement a paging type setup and be able to say what the total
    number of rows there are.

    Any thoughts about how I should go about this?

    --
    Michael Tissington
    Oaklodge Technologies
    http://www.oaklodge.com/technology



    Michael Guest

  2. #2

    Default Large recordsets ...

    This is not a sql question but an ADO question.

    1)In ADO you can use RecordCount to Return the total
    number of record. If you want to do it in sql use COUNT(*)
    in your statement.

    2)In ADO, I believe if you use client side cursor you can
    specify how many record to get at a time.

    3)On keypress fire the query and put
    WHERE LastName like Letter + '%'

    Hope this helps.
    Review ADO
     
    of interfaces that 
    recordset and scroll 
    thousands of records. 
    following 
    user presses a 
    with that letter. 
    would be easy. 
    head around is 
    say what the total 
    Laurent Guest

  3. #3

    Default Re: Large recordsets ...


    "Michael Tissington" <com> wrote in message
    news:OTAU$phx.gbl... 
    scroll 
    total 

    A query of the form

    select * from
    (
    select top 20 * from
    (
    select top 100 *
    from my_table
    order by a,b,c
    ) dt
    order by a desc, b desc, c desc
    ) dt1
    order by a,b,c

    will return rows 81 through 100 relative to the ordering a,b,c.

    But it's not very efficient.

    David


    David Guest

  4. Moderated Post

    Default Re: Large recordsets ...

    Removed by Administrator
    Greg Guest
    Moderated Post

Similar Threads

  1. Arg the recordsets!
    By Crazy in forum Dreamweaver AppDev
    Replies: 2
    Last Post: August 19th, 09:54 PM
  2. Replies: 5
    Last Post: February 16th, 09:04 AM
  3. Replies: 2
    Last Post: June 1st, 03:43 AM
  4. Rendering large recordsets
    By Ken McAndrew in forum ASP Database
    Replies: 6
    Last Post: April 6th, 05:02 AM
  5. Replies: 0
    Last Post: January 13th, 11:03 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