Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 8 of 8
  1. #1
    New to the CF scene
    Join Date
    Aug 2005
    Location
    Melbourne, Australia
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    advice on recordsets

    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;
    recordset.close
    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.

  • #2
    Senior Coder Spudhead's Avatar
    Join Date
    Jun 2002
    Location
    London, UK
    Posts
    1,856
    Thanks
    8
    Thanked 110 Times in 109 Posts
    Are you closing your connection object and setting it to nothing, too? That's probably even more vital than your recorsets.

  • #3
    Senior Coder
    Join Date
    Jun 2002
    Location
    Zwolle, The Netherlands
    Posts
    1,120
    Thanks
    2
    Thanked 31 Times in 31 Posts
    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.
    I am the luckiest man in the world

  • #4
    New to the CF scene
    Join Date
    Aug 2005
    Location
    Melbourne, Australia
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    thanks guys,

    roelf,
    could you elaborate on useing the db a little smarter?

    thanks again in advance.

  • #5
    Senior Coder
    Join Date
    Jun 2002
    Location
    Zwolle, The Netherlands
    Posts
    1,120
    Thanks
    2
    Thanked 31 Times in 31 Posts
    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.

    Example:
    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)
    I am the luckiest man in the world

  • #6
    Regular Coder
    Join Date
    May 2005
    Location
    Michigan, USA
    Posts
    566
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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)

    Code:
    Set RST.ActiveConnection = Nothing
    Also note that locks can happen based on what paramaters you send it in the open command.
    Note: I do not test code. I just write it off the top of my head. There might be bugs in it! But if any thing I gave you the overall theory of what you need to accomplish. Also there are plenty of other ways to accomplish this same thing. I just gave one example of it. Other ways might be faster and more efficient.

  • #7
    Regular Coder
    Join Date
    Sep 2004
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  • #8
    New to the CF scene
    Join Date
    Aug 2005
    Location
    Melbourne, Australia
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

    Cheers.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •