Handling Database Connections and Recordsets In Classic ASP...or any other Scripting Language

In my continuing saga of best practices posts brought on by things I see in my latest contract, I got to thinking about database connections and as I mentioned yesterday, recordset handling. Due the the current contract, it centers around Classic ASP.

I am seeing in this application, very bad (in my opinion) practice. They are, within each function that hits the database, opening and closing connections. Pass some info to a function, open a db connection, get the info you need and close it, passing the needed info back to the requesting code.

Now, I have no formal programming education, and no formal IT education, I only draw on my decade+ years of development experience to say this is a bad idea.

The single most expensive piece of code is the connection and query to a database. Constantly opening and closing queries is going to use a lot of resources. In a language like PHP one doesn't have to generally concern themselves with the connection, as you simply instantiate the connection, and, when the script gets to the end, the connection closes. With Classic ASP this is not so, you need to explicitly close the connection.

That being said, the best course of action, as my experience has shown me is to open a connection to the database before HTML headers have even been sent, do all your queries, concatenate all your data into variables, then close the connection. At that point, send your HTML headers, HTML code, and populate the code with your variables of data where necessary.

Some more technical guru type people may argue with this for some reason or another, but to me, this is the most efficient use of resources, as you open the database connection once, do all your recordset handling (hopefully with the getRows() function) and build all the arrays, code and variables as necessary, then close the connection as soon as possible, freeing up all associated resources. From there you enter the presentation layer of the code, running in all ASP mode, no database interactivity at all.

Using this same methodology in any scripting language you can benefit from the performance gains as well. While in PHP you don't need to close database connections explicitly, you most certainly can, and in some very high traffic situations, I have had to as well.

At the end of the day, it's not the platform you are using, the same technical details in the background remain constant. The quicker you open and close connections, and the fewer you have, the better.

Submitted for your consideration and reading pleasure.

Happy coding.