ASP code bombs out after SQL retrieve with over thousands of records

Discussion in 'ASP General' started by LetMeDoIt, May 18, 2007.

  1. LetMeDoIt

    LetMeDoIt Guest

    Greetings,

    I'm using ASP to retrieve from MSSQL and I then populate a table.
    After several months of successfull retrieves, this same code now
    bombs out. It turns out that if I clear out from SQL about 10,000
    records, the retrieve is then successfull.

    Reading some of the posts, I believe I need to set the cache. If
    anyone can point out where that cache, it would be greatly
    appreciated...

    regards, letmedoit.
     
    LetMeDoIt, May 18, 2007
    #1
    1. Advertising

  2. LetMeDoIt wrote:
    > Greetings,
    >
    > I'm using ASP to retrieve from MSSQL and I then populate a table.
    > After several months of successfull retrieves, this same code now
    > bombs out. It turns out that if I clear out from SQL about 10,000
    > records, the retrieve is then successfull.
    >
    > Reading some of the posts, I believe I need to set the cache. If
    > anyone can point out where that cache, it would be greatly
    > appreciated...
    >


    Just posting to echo Dave's comments, and to add:
    Why are you retrieveing ALL the records in your database table? You should
    be using a WHERE clause to limit the number of records retrieved. What user
    is going to want to browse through thousands of records? At the very least,
    consider using a paging solution such as described here:
    http://www.aspfaq.com/show.asp?id=2120

    You might also wish to consider generating a summary of the records and
    displaying that to the users instead of the mass of details.

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows [MVP], May 19, 2007
    #2
    1. Advertising

  3. LetMeDoIt

    LetMeDoIt Guest

    On May 19, 9:41 am, "Bob Barrows [MVP]" <>
    wrote:
    > LetMeDoIt wrote:
    > > Greetings,

    >
    > > I'm usingASPto retrieve from MSSQL and I then populate a table.
    > > After several months of successfull retrieves, this samecodenow
    > >bombsout. It turns out that if I clear out from SQL about 10,000
    > > records, the retrieve is then successfull.

    >
    > > Reading some of the posts, I believe I need to set the cache. If
    > > anyone can point out where that cache, it would be greatly
    > > appreciated...

    >
    > Just posting to echo Dave's comments, and to add:
    > Why are you retrieveing ALL the records in your database table? You should
    > be using a WHERE clause to limit the number of records retrieved. What user
    > is going to want to browse through thousands of records? At the very least,
    > consider using a paging solution such as described here:http://www.aspfaq.com/show.asp?id=2120
    >
    > You might also wish to consider generating a summary of the records and
    > displaying that to the users instead of the mass of details.
    >
    > --
    > Microsoft MVP -ASP/ASP.NET
    > Please reply to the newsgroup. This email account is my spam trap so I
    > don't check it very often. If you must reply off-line, then remove the
    > "NO SPAM"


    My apologies for the cryptic paragraph. Thanks for the responses.

    To answer a few questions that arose earlier:
    1- There is a WHERE clause for the users, which when used, works
    well. The users enter a specific string, and upon selecting the
    RETRIEVE button, records are returned and put in columns and rows.
    This works as expected.
    2- By clearing out records from the SQL database, I mean, I actually
    delete these records by typing the SQL command "delete from mytable
    where..."
    3- There are instances where a few users may want to see all records,
    that is why that option is there. Currently, typing % and selecting
    RETRIEVE from a button fetches all records
    4- What I mean by "Bombing out" is that when all records are retrieved
    (ie, when % is entered), instead of each records going to each row/
    column within the table I created, these records are 'dumped' into my
    html page (as in there are no rows and columns, thus no carriage
    returns and line feed, each records continues from the same row from
    the previous record, etc)

    Regards.
     
    LetMeDoIt, May 21, 2007
    #3
  4. LetMeDoIt wrote:
    > On May 19, 9:41 am, "Bob Barrows [MVP]" <>
    > wrote:
    >> LetMeDoIt wrote:
    >>> Greetings,

    >>
    >>> I'm usingASPto retrieve from MSSQL and I then populate a table.
    >>> After several months of successfull retrieves, this samecodenow
    >>> bombsout. It turns out that if I clear out from SQL about 10,000
    >>> records, the retrieve is then successfull.

    >>
    >>> Reading some of the posts, I believe I need to set the cache. If
    >>> anyone can point out where that cache, it would be greatly
    >>> appreciated...

    >>
    >> Just posting to echo Dave's comments, and to add:
    >> Why are you retrieveing ALL the records in your database table? You
    >> should
    >> be using a WHERE clause to limit the number of records retrieved.
    >> What user
    >> is going to want to browse through thousands of records? At the very
    >> least,
    >> consider using a paging solution such as described
    >> here:http://www.aspfaq.com/show.asp?id=2120
    >>
    >> You might also wish to consider generating a summary of the records
    >> and
    >> displaying that to the users instead of the mass of details.
    >>
    >> --
    >> Microsoft MVP -ASP/ASP.NET
    >> Please reply to the newsgroup. This email account is my spam trap so
    >> I
    >> don't check it very often. If you must reply off-line, then remove
    >> the "NO SPAM"

    >
    > My apologies for the cryptic paragraph. Thanks for the responses.
    >
    > To answer a few questions that arose earlier:
    > 1- There is a WHERE clause for the users, which when used, works
    > well. The users enter a specific string, and upon selecting the
    > RETRIEVE button, records are returned and put in columns and rows.
    > This works as expected.
    > 2- By clearing out records from the SQL database, I mean, I actually
    > delete these records by typing the SQL command "delete from mytable
    > where..."
    > 3- There are instances where a few users may want to see all records,
    > that is why that option is there. Currently, typing % and selecting
    > RETRIEVE from a button fetches all records
    > 4- What I mean by "Bombing out" is that when all records are retrieved
    > (ie, when % is entered), instead of each records going to each row/
    > column within the table I created, these records are 'dumped' into my
    > html page (as in there are no rows and columns, thus no carriage
    > returns and line feed, each records continues from the same row from
    > the previous record, etc)
    >
    > Regards.


    By deleting records, you are effectively limiting the number of records
    the user can retrieve. My suggestion would be to make this explicit by
    using the TOP n keyword in your query. Combined with a paging solution,
    you should avoid the problem described above.

    Without knowing how to reproduce your symptoms, we have no chance of
    being able to tell you what the problem is. It is probably related to
    the technique you are using to extract the data values from the
    recordset and write them to response.

    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
     
    Bob Barrows [MVP], May 21, 2007
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. John
    Replies:
    3
    Views:
    381
    John Saunders
    Jul 21, 2004
  2. R. Alan Monroe
    Replies:
    8
    Views:
    566
    John J. Lee
    Sep 26, 2003
  3. Kiran
    Replies:
    4
    Views:
    194
    Kiran
    Oct 14, 2003
  4. Xeno Campanoli
    Replies:
    2
    Views:
    124
    Phrogz
    Jul 1, 2005
  5. Replies:
    0
    Views:
    54
Loading...

Share This Page