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

L

LetMeDoIt

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

Bob Barrows [MVP]

LetMeDoIt said:
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.
 
L

LetMeDoIt

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

Bob Barrows [MVP]

LetMeDoIt said:
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.
 

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top