Software Developer / Programmer Next Step Systems US-IL-Des Plaines Post A Job | Post A Resume

Explicitly Freeing your ADO Objects

---

One of life's few truisms is that programmers are inherently lazy. This fact gets us into trouble more often than not, and can get us into extremely hot water when dealing with database connectivity with ASP.

In ASP, we create connections to databases and capture the results of database queries using ADO; we use ADODB.Connection and ADODB.Recordset, most frequently. Let's look at a quick example of how we might use these two:

Dim objConn
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString = "DSN=Northwind"
objConn.Open

Dim objRS
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open "SELECT * FROM Table1", objConn


(If you're unfamiliar with database-connectivity, be sure to read this article.)

We'd then go along our merry way, iterating through objRS. Once we're done using objRS, what do we do? Most often, developers do nothing, they let ASP "clean up." When the Server.CreateObject calls are made, resources are allocated on the server to handle new instances of these objects. If we don't explicitly inform the server that we're done with these allocated resources, ASP should do it for us. Putting all of our faith into ASP is a bit of a risk. It's much safer and much more reliable to explicitly close and clean up the instances of our recordset and connection objects.

So, how do we explictly close our objects and free the memory associated with them? All we need to do is invoke these following four lines when we're finished utilizing our two objects:

objRS.Close
Set objRS = Nothing

objConn.Close
Set objConn = Nothing


This will force the cleanup, rather than relying on the automated, implicit cleanup. Now, you may be wondering how important it is to do this. Who wants to have to write four extra lines of code for each ASP page that utilizes database connectivity? Well, the advantages to doing it far outweigh the disadvantages of having to do it. To make my point, let me quote Charles Carroll, webmaster of ActiveServerPages.com:

"You must close recordset, set to nothing, close connections and set to nothing in that sequence. The standard garbage collection is incomplete and unreliable. [By garbage collection, Charles is referring to the implicit cleanup of allocated resources on the server.]

"DataReturn[, an ASP webhosting company,] has many sites that fail horribly if they let IIS do automatic garbage collection. Adding Close/Set Nothing makes the sites work like champs again. It is mandatory in any high volume site."

If Charles's words didn't convince you, let me discuss a real world problem that was caused by a lack of explicit memory reallocation calls. Brian Fairchild runs a high-volume ASP site that uses Access as the database backend. He noted after a while, ASP pages would simply stop responding! HTML pages would display fine, but all ASP pages would simply not process; a reboot of the server was needed. Eventually, Brian found out that by explicitly closing and freeing all of his recordset and connection objects, the problems stopped. ( Read Brian's posts to the messageboard regarding this issue.)

Well, lazy coders out there, I hope this article has convinced you to spend the extra few seconds to explicitly close your ADO objects. I have trouble myself, taking the few moments and writing those needed lines!

Happy Programming!

Windows Internet Technology | ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article