Oracle: OLEDB Resource(Session) Pooling
by Eric Ma EMa@ompus.jnj.com
Database connectivity is a prerequisite for today's dynamically generated web pages. However, connection to databases is one of the most expensive operations one does from within an ASP page, because of the overhead involved in database user authentication and allocation of database resources to handle user interaction with the database, among other things. All these can add significant latency to your web site. Based on the recent discussions in this list, it is apparent that everyone is keenly aware of this issue and would like to minimize the negative impact on performance by having to connect to databases. For a programmer with a client/server programming background, it is natural for the person to design a solution based on using a database connection that persists through an entire user session. However, for a web-based application, this is a big no-no (see the article at /advice/dbsessionapp.asp for reasons why you should never do this).
The best practice for database connection (we limit our discussion to Oracle here) from an ASP application is to open the connection at late as you can, and close it as soon as you can, which means you open and close database connection on every ASP page. The encouraged practice is to use just one set of Oracle username/password for your entire application, not one set for each user as you may be used to do with client/server applications. Access control is no longer performed by the database, but by your application. You can save the ADO connection string in an application variable in the global.asa file. Some other alternatives include saving it in the windows Registry, or use an include file that has the ADO connection information, and include that file in the pages where database connection is required.
Opening a new database connection for each ASP page may not be as bad as you think, because from ADO 2.0 up you can utilize the "resource (session) pooling" feature offered by Microsoft's OLE DB Provider for Oracle. Resource pooling is similar to ODBC connection pooling, where a connection is returned to a pool instead of being destroyed immediately after it is closed and set to nothing in your code. See the following article for more details: