Connecting to a database server typically consists of several time-consuming steps. A physical channel such as a socket or a named pipe must be established, the initial handshake with the server must occur, the connection string information must be parsed, the connection must be authenticated by the server, checks must be run for enlisting in the current transaction, and so on.
To minimize the cost of opening connections, ADO.NET uses an optimization technique called connection pooling, which minimizes the cost of repeatedly opening and closing connections. Connection pooling is handled differently for the .NET Framework data providers.
How It Works
Connection pooling involves the pooling of open database connectivity (ODBC) connections to reduce the frequency at which ODBC connections need to be opened and closed on heavily accessed servers. Connection pooling improves ASP performance for ODBC-enabled Web applications and provides a graceful way to manage connection timeouts.
To use ODBC connection pooling on IIS, perform the following steps:
- Configure the database driver using ODBC in Control Panel.
- Enable connection pooling in the Microsoft Windows NT registry.
- Open individual connections in your Microsoft ActiveX Data Objects (ADO) code right before data access is needed for an ASP page, and release connections as soon as the data has been accessed.
Once connection pooling is enabled, the ODBC driver will check the connection pool for idle connections it can reuse before creating a new connection in response to an ODBC request. When connections are released, they are returned to the connection pool instead of being closed.
Adding Connections
A connection pool is created for each unique connection string. When a pool is created, multiple connection objects are created and added to the pool so that the minimum pool size requirement is satisfied. Connections are added to the pool as needed, up to the maximum pool size specified (100 is the default). Connections are released back into the pool when they are closed or disposed.
Removing Connections
The connection pooler removes a connection from the pool after it has been idle for approximately 4-8 minutes, or if the pooler detects that the connection with the server has been severed. Note that a severed connection can be detected only after attempting to communicate with the server. If a connection is found that is no longer connected to the server, it is marked as invalid. Invalid connections are removed from the connection pool only when they are closed or reclaimed.
If a connection exists to a server that has disappeared, this connection can be drawn from the pool even if the connection pooler has not detected the severed connection and marked it as invalid. This is the case because the overhead of checking that the connection is still valid would eliminate the benefits of having a pooler by causing another round trip to the server to occur. When this occurs, the first attempt to use the connection will detect that the connection has been severed, and an exception is thrown.
