Connection pooling enables an application to use a connection from a pool of connections that do not need to be reestablished for each use. Once a connection has been created and placed in a pool, an application can reuse that connection without performing the complete connection process.
Using a pooled connection can result in significant performance gains, because applications can save the overhead involved in making a connection. This can be particularly significant for middle-tier applications that connect over a network, or for applications that repeatedly connect and disconnect, such as Internet applications.
In addition to performance gains, the connection pooling architecture enables an environment and its associated connections to be used by multiple components in a single process. This means that stand-alone components in the same process can interact with each other without being aware of each other. A connection in a connection pool can be used repeatedly by multiple components.
Note Connection pooling can be used by an ODBC application exhibiting ODBC 2.x behavior, as long as the application can call SQLSetEnvAttr. When using connection pooling, the application must not execute SQL statements that change the database or the context of the database, such as changing the <database name>, which changes the catalog used by a data source.
The connection pool is maintained by the Driver Manager. Connections are drawn from the pool when the application calls SQLConnect or SQLDriverConnect, and are returned to the pool when the application calls SQLDisconnect. The size of the pool grows dynamically based upon the requested resource allocations. It shrinks based on the inactivity timeout: If a connection is inactive for a period of time (it has not been used in a connection), it is removed from the pool. The size of the pool is limited only by memory constraints and limits on the server.
The Driver Manager determines whether a specific connection in a pool should be used according to the arguments passed in SQLConnect or SQLDriverConnect, and the connection attributes set after the connection was allocated.
When the Driver Manager is pooling connections, it needs to be able to determine if a connection is still working before handing the connection out. Otherwise, the Driver Manager keeps on handing out the dead connection to the application whenever a transient network failure occurs. In ODBC 3.x a new connection attribute, SQL_ATTR_CONNECTION_DEAD, has been defined. This is a read-only connection attribute that returns either SQL_CD_TRUE or SQL_CD_FALSE. The value SQL_CD_TRUE means that the connection has been lost, while the value SQL_CD_FALSE means that the connection is still active. (Drivers conforming to earlier versions of ODBC can also support this attribute.)
A driver must implement this option efficiently, or it will impair the connection pooling performance. Specifically, a call to get this connection attribute should not cause a round trip to the server. Instead, a driver should just return the last known state of the connection. The connection is dead if the last trip to the server failed, and not dead if the last trip succeeded.
To use a connection pool, an application performs the following steps:
Note How a requested connection is matched to a pooled connection is determined by the SQL_ATTR_CP_MATCH environment attribute. For more information, see SQLSetEnvAttr.