OLE DB Resource (session) pooling (using connection pooling in ADO development)

Ado|session

Oracle:oledb Resource (session) pooling
by Eric Ma EMa@ompus.jnj.com

Database connectivity is a prerequisite to today's dynamically generated Web pages. However, connection to databases are 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 the database, handle user interaction with the Database, among other things. All this can add significant latency to your Web site. Based on the "recent discussions in" This list, it is apparent ' everyone is keenly aware of ' this issue and would Minimize the negative impact on performance by have to connect to databases. For a programmer with a client/server programming background, it are natural for the person to design a solution based on U Sing a database connection that persists through a entire user session. However, for a web-based application, this are a big no-no ("the article at/advice/dbsessionapp.asp  for reasons W Hy you should never doing this).

The best practice for database connection (we limit my discussion to Oracle) from a ASP application is to open t He connection at late as can, with close it as soon as you can, which means with open and close database connection on E Very ASP page. The encouraged practice is to use just one set of Oracle Username/password for your entire application, not one set for EA CH user as you are used to does with client/server applications. Access control are no longer performed by the database, but by your application. You can save the ADO connection string in a application variable in the Global.asa file. Some other alternatives include saving it in the Windows Registry, or use a include file that has the ADO connection info Rmation, and include that file in the pages where database connection is required.

Opening A new database connection for each ASP page may is as bad as you do, because from ADO 2.0 up can UTI Lize the "resource pooling" feature offered by Microsoft's OLE DB Provider for Oracle. Resource pooling is similar to ODBC connection pooling, where a connection was returned to a pool instead of being D immediately after it are closed and set to nothing in your code. The following article for more details:
http://msdn.microsoft.com/library/techart/pooling2.htm.

The purpose of this FAQ are to demonstrate how to properly set the environment on your IIS/NT server to take advantage O F OLE DB resource pooling. Unlike with ODBC, where your can enable pooling and set the timeout parameter from the 32-bit ODBC utility into the control P Anel, with OLE DB your have to directly work on the Registry itself. Again, this is not as scary as it sounds. To did so, your determine the version of OLE DB Provider you are using by running the script found At:http://www.lear Nasp.com/learn/connectioninfo.asp, then dependent on the version of your have, you could do the following steps:

? If you are using the OLE DB 2.0 provider can do resource pooling but you cannot modify the default timeout Configurati On (seconds). To set up resource pooling, add the following subkey in the Registry:
Hkey_classes_root\clsid\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\oledb_services. Make sure the DWORD type, and enter a hexadecimal value of 0xFFFFFFFF.

However, I suggest that you don ' t use the 2.0 version of the Provider any more because of potential memory. The following KB article for more:
Http://support.microsoft.com/support/kb/articles/q194/3/87.asp

? If you use the OLE DB 2.1 or 2.5 provider, in addition to doing the above step, you can configure the pooling timeout Meter by adding the following subkey:
Hkey_classes_root\clsid\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\sptimeout. Again This entry was of a DWORD type and you enter a decimal value in seconds. It is up to decide the number of second for you want to set, depending on how busy your site is and how frequent the PA GES need to access Oracle data.

A KB article describing the above step can is found at:
Http://support.microsoft.com/support/kb/articles/q237/9/77.asp

After your make the above changes, reboot your NT Server to make Registry changes. This is the necessary.

Congratulations, now you are using the wonderful feature of OLE DB resource pooling! I hope from "Today" on "You'll Never ever" about caching connection objects in session variables anymore! Treat it as evil!

Some Side Notes and observations:

? With Oracle, in order to use resource pooling, your need to add the Registry subkey. Has only the OLE DB Services =-1 "In your connection the string is not enough.

? I came across the following KB article in MSDN saying OLE DB resource pooling are Automatica