Data Access and Transactions

ODBC Connection Pooling

Database applications that frequently open and close connections can reduce database server performance. Fortunately, ODBC 3.5 implements connection pooling, which enables applications to share connections across user requests, and thereby improves performance by reducing the number of idle connections.

Before making a new connection to an ODBC data source, the ODBC driver searches the connection pool for any idle connections that may satisfy the connection request. For the connection to be reused, the connection string and user context of the pooled connection must exactly match that of the request. If a matching idle connection is found, it is returned to the calling application.

When an ODBC connection is released, the connection returns to the pool, rather than being closed. The Connection Pool Timeout (CPTimeout) setting of your ODBC driver determines the length of time that a connection remains in the connection pool. If the connection is not reused during this time, it is closed and removed from the pool. The default CPTimeout value is 60 seconds.

You can modify the CPTimeout value of each ODBC database driver by changing the following Windows 2000 Server registry key:

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

Caution Do not use a registry editor to edit the registry directly unless you have no alternative. The registry editors bypass the standard safeguards provided by administrative tools. These safeguards prevent you from entering conflicting settings or settings that are likely to degrade performance or damage your system. Editing the registry directly can have serious, unexpected consequences that can prevent the system from starting and require that you reinstall Windows 2000. To configure or customize Windows 2000, use the programs in Control Panel or Microsoft Management Console (MMC) whenever possible.

For example, the following setting configures the ODBC driver for the SQL Server connection pool time-out to 180 seconds:

HKEY_LOCAL_MACHINE\SOFTWARE
\ODBC
\ODBCINST.INI
\SQL Server
\CPTimeout = "180"

A severed connection to the database could prevent applications that use a connection pool from connecting successfully. Client applications would continue to make new connection requests even though the connection is broken. It takes time for each request to determine that the server is unavailable, and new connection requests must wait for others to time out. Eventually, the server will be unable to accept any more requests.

The ODBC Connection Manager can be configured to retry dead connections on a preset interval. If the connection attempt fails, the connection is marked as “bad” and placed back in the pool. Once a bad server has been identified, subsequent connection requests for that server immediately return an error. Periodically, the Connection Manager attempts to reestablish the connection. If the attempt succeeds, the connection returns to a valid state and normal processing resumes.

You can configure the Retry Wait property for a specific ODBC driver by creating a registry key with the following settings:

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

The following setting instructs the ODBC driver for SQL Server Connection Manager to retry a dead connection after a 60-second wait:

HKEY_LOCAL_MACHINE\SOFTWARE
\ODBC
\ODBCINST.INI
\SQL Server
\Retry Wait = "60"

Every process uses its own connection pool. If you are using an out-of-process component (or LocalServer application), you cannot take full advantage of ODBC connection pooling. Because each application process uses a separate pool, your application can only share connections with itself. In order to share connections with other components, you must write your business logic as a DLL. OLE DB providers automatically handle connection pooling.