While caching data in the Application
or Session
object can be a good idea, caching database connections is usually a bad one.
Take for example the Connection
object, if you store a connection in a Session
object, you no longer have the benefit of connection pooling. Connection pooling
is beneficial when connections are shared across multiple clients and resources
are in use only as long as they are needed i.e. If the Connection
object is
stored in the ASP Session
object, then a database connection will be created for
every user. Similarly, if one Connection
object is stored in the Application
object and used on all pages, then all pages will contend for use of this
connection. This puts unnecessarily high stress on both the Web server and the
database.
Instead of caching database connections, create
and destroy ADO objects on every ASP page that uses ADO. This is efficient
because IIS has database connection pooling built in. More accurately, IIS
automatically enables OLEDB and ODBC connection pooling. This ensures that
creating and destroying connections on each page will be efficient.
Since connected recordsets store a reference to a
database connection, it follows that you should not cache connected recordsets
in the Application
or Session
objects. However, you can safely cache
disconnected recordsets, which don't hold a reference to their data connection.
To disconnect a recordset, take the following two steps:
Set rs = Server.CreateObject("ADODB.RecordSet")
rs.CursorLocation = adUseClient
rs.Open strSQL, strProv
rs.ActiveConnection = Nothing