Connection Pooling

Traditional database applications create a single connection to the database that is used for the duration of the application. Because of the stateless nature of the Web, however, a Web-based database application must open and close a new connection on each page.

One method to create persistent database connections would be to create a database connection for each user and store the connection in the Session object. However, because this method increases the number of idle connections to the database it is recommended only for low-traffic web sites.

A more efficient way to handle Web-based database connections is to use the connection pooling feature of ODBC 3.0, which maintains open database connections and manages connection sharing across different user requests to maintain performance and to reduce the number of idle connections. On each connection request, the connection pool first determines if there is an idle connection in the pool. If so, the connection pool returns that connection instead of making a new connection to the database. Connections are disconnected from the database and taken out of the pool if they remain idle for longer than 60 seconds.

Connection pooling is enabled by default in ASP. You can disable connection pooling by setting the registry entry, StartConnectionPool, to zero. For more information about registry entries, see Configuring Registry Entries in the Active Server Pages Scripting Guide.

To make the best use of connection pooling, you should open and close the connection to your database on every ASP page. However, you should specify the connection attributes once and reuse them on every page. For example, you can use a "connection string" in the Global.asa file to specify the connection attributes in the Session_OnStart event, such as in the following script:

Session("ConnectionString") = "dsn=AdvWorks;uid=advworks;pwd=advworks"
 

Then in each ASP file that accesses the database, you can write:

<OBJECT RUNAT=Server ID=Conn PROGID="ADODB.Connection"></OBJECT>
 

to create an instance the connection object for the page, and use the script:

Conn.Open Session("ConnectionString")
 

to open the connection. At the end of the page, you close the connection with:

Conn.Close
 

For a more complete example of using connection pooling, see the Adventure Works Sample site.

The default timeout value for connection pooling is 60 seconds. You can change this value for an ODBC driver by creating a registry key with the following settings:

\HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\driver-name\CPTimeout = timeout  (REG_SZ, units are in seconds)
 

For example, the following key sets the connection pool timeout to 30 seconds for the SQL Server driver.

\HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\SQL Server\CPTimeout = 30
 

The following key sets the connection pool timeout to 10 seconds for the Microsoft Access Driver.

\HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\Microsoft Access Driver (*.mdb)\CPTimeout = 10
 

Note For the preceeding registry settings, the value of the timeout is of type REG_SZ, not REG_DWORD.

© Microsoft Corporation. All rights reserved.