View Full Version : advice on recordsets

08-09-2005, 06:30 AM
Hi All,

I have an asp driven site with a sqlserver db that keeps causing iis to crash.

I think i may of tracked down the problem to my recordset's

currently the site draws alot of data from the db through a SQLOLEDB connection.

I noticed that in the crash logs for iis the first page to receive a request error was an asp page that had multipile record sets in the one page. (it is not isolated to one page)

to get to my point, how important is it to close all record sets and also clear them?

i have added the following code to a number of pages;
set recordset = nothing

(i know this should have been there to start with but im only maintaning the site, someone else wrote it)

is there anything else i can do to prevent the recordsets from causeing iis to crash?

thanks in advance.

08-09-2005, 02:06 PM
Are you closing your connection object and setting it to nothing, too? That's probably even more vital than your recorsets.

08-09-2005, 03:13 PM
perhaps your pages can be redesigned so they use the database a little smarter. I've seen a lot of DB-related pages which can be speeded up and made less resource-hungry, by just rethink the way the db is used.

08-10-2005, 02:17 AM
thanks guys,

could you elaborate on useing the db a little smarter?

thanks again in advance.

08-10-2005, 11:53 AM
Well, you say you use multiple recordsets on one page. What i have seen a few times is also a lot of recordsets, where one recordset could be used.

a page calls a recordset where each record matches certain criteria. this same page, while looping through the first recordset, getting a specific value from a field, based on this fieldvalue, another sql is build, fired to the database, retrieving another recordset etcetera

All this could be done most of the time using a better sql for the first recordset, retrieving all the neccesary data in one recordset. Better performance (each trip to the database costs time)

08-10-2005, 01:51 PM
When he says a little smarter, it is really hard to teach it to some one without database training. You need to start using fancy SQL to get what you need. Crashes usually happen when there are to many locks on the database. A regular Recordset keeps the information locked so that only it can use the data. And if you are using a large chunk of data this can make other systems upset, or even the same webpage’s opened by a different users upset. In good practice it is not only necessary to close all connections but it is also necessary to close all ACTIVECONNECTIONS. It is hard to find any thing about this online if you do not know what you are looking for. (PS. This will also speed up your web pages)

Use this if you are not returning a non linked recordset, and run this line right after opening the recordset. (aka not one returned from a sp)

Set RST.ActiveConnection = Nothing

Also note that locks can happen based on what paramaters you send it in the open command.

08-10-2005, 03:18 PM
Another option is once you have returned a recordset from the database to store it into an array and close the recordset. Here is an article describing how to do that: http://www.aspfaqs.com/aspfaqs/ShowFAQ.asp?FAQID=190

08-11-2005, 03:20 AM
thanks guys,

i will take all this on board,

i was also thinking of trying to setup stored procedure in sqlserver to take some of the load from the pages.