In Focus: Connection Pooling

Connection pooling, which Microsoft introduced in Open Database Connectivity (ODBC) 3.0, lets an ODBC application reuse a connection within the same shared environment (henv) without having to perform the complete connection process. In addition to performance gains from using less overhead, the connection pooling architecture lets multiple components in one process use one environment and its associated connections. This configuration means that standalone components in the same process can interact without being aware of one another.

In other words, connection pooling makes ODBC more efficient, especially for online transaction processing (OLTP) and Web applications, which require several connections. You can use connection pooling if you're using ODBC 2.x-level drivers, as long as the ODBC Driver Manager is version 3.0 or later. However, different henv or different drivers can never reuse a connection. Another restriction is that you can't use connection pooling with the Microsoft Access driver because internally it uses the Jet database engine. Jet supports the apartment-threaded model, in which the same thread must handle connect and disconnect procedures.

Microsoft articles Q164221, "How to Enable Connection Pooling in an ODBC Application" (http://support.microsoft.com/support/kb/articles/q164/2/21.asp), and Q166083, "How to Enable Connection Pooling in an OLE DB Application" (http://support.microsoft.com/support/kb/articles/q166/0/83.asp), provide helpful examples of enabling connection pooling. Visual Basic (VB) developers can use connection pooling by calling the SQLSetEnvAttr function

Declare Function SQLSetEnvAttr Lib "odbc32.dll" (ByVal henv As Long, ByVal Attribute As Long, ByVal Value As Long, ByVal StringLength As Long) As Integer

Microsoft Transaction Server (MTS) automatically implements connection pooling. However, Internet Information Server (IIS) 3.0 or later supports connection pooling for Active Server Pages (ASPs) or ActiveX Data Objects (ADOs) only if you change the StartConnectionPool Registry setting to 1. (StartConnectionPool is located in the HKEY_LOCAL_MACHINESYSTEMCurrentControlSetServicesW3SVCASPParameters Registry key.)

Although developers can't control the number of connections in the pool or monitor the connections, they can set how long a connection remains in the pool. To modify this setting, developers can set the ODBC driver's CPTimeout property (the default is 60 seconds) by calling SQLConfigDriver.

The original ODBC 3.0 Driver Manager didn't detect bad connections, but ODBC 3.0 Service Pack 1 (SP1) introduced ODBCSetTryWaitValue, an API that prevents the ODBC Driver Manager from repeatedly attempting to connect to an unavailable server. ODBCSetTryWaitValue takes a DWORD parameter and saves information in the HKEY_LOCAL_MACHINESOFTWAREODBCODBCINST.INIODBC Connection PoolingRetry Wait Registry key. Microsoft article Q168250, "IIS Performance Degrades with a Bad Connection" (http://support.microsoft.com/support/kb/articles/q168/2/50.asp), provides additional information.

ODBC technology has come a long way since its inception in 1992, when its standard API represented a breakthrough in data access. Today, ODBC is trusted, omnipresent, and virtually as fast as native driver access, thanks in part to refinements such as connection pooling.