Connection Pooling in ADO.NET Applications
Introduction
Connecting to a database is the single slowest operation inside a data-centric application. Efficiently managing connections through connection pooling is important to application performance. ADO.NET connection pooling allows the reuse of connections rather than the creation of new ones every time the data provider needs to establish a connection to the underlying database.
For most ADO.NET data providers controlling connection pooling behavior is achieved by using connection string options. With DataDirect Connect for .NET it is possible to define and increase the number of connection pools, the number of connections in a pool and the lifetime of pooled connections used by each process.
Connection pooling in ADO.NET is not provided by the core components of the .NET framework. It must be implemented in the .NET data provider itself. All DataDirect .NET data providers offer this improved level of connection pooling functionality. By thoughtfully handling connection management before implementation, you can improve application performance and maintainability.
DataDirect Connect for .NET is the industry's only suite of ADO.NET data providers with a 100% managed architecture, eliminating the need for database clients, boosting performance and delivering a flexible, secure connection to Oracle, DB2, Microsoft SQL Server, and Sybase.
ADO.NET Connection Pooling Topics: (Click to view topic.)
- Creating a Connection Pool
- Using Integrated Windows (NTLM) Authentication with SQL Server
- Adding Connections to a Pool
- Removing Connections from a Pool
- Handling Dead Connections in a Pool
- Handling Distributed Transactions
- Tips on Opening and Closing Connections
- Tracking Connection Pool Performance
Creating a Connection Pool
Each connection pool is associated with a specific connection string. By default, the connection pool is created when the first connection with a unique connection string connects to the database. The pool is populated with connections up to the minimum pool size. Additional connections can be added until the pool reaches the maximum pool size.
The pool remains active as long as any connections remain open, either in the pool or used by an application with a reference to a Connection object that has an open connection.
If a new connection is opened and the connection string does not exactly match an existing pool, a new pool must be created. By using the same connection string, you can enhance the performance and scalability of your application.
In the following C# code fragment, three new OracleConnection objects are created, but only two connection pools are required to manage them. Note that the connection strings for conn1 and conn2 differ by the values assigned for User ID, Password and Min Pool Size connection string options.OracleConnection conn1 = new OracleConnection1();
Conn1.ConnectionString = "Service Name=ORCL;Host=Accounting;
User ID=scott;password=tiger;Min Pool Size=50";
conn1.Open();
// Pool A is created.
OracleConnection conn2 = new OracleConnection2();
conn2.ConnectionString = "Service Name =ORCL;Host=Accounting;
User ID=lucy;password=quake;Min Pool Size=20";
conn.Open(); //
Pool B is created because the connection strings
// differ.
OracleConnection conn3 = new OracleConnection3();
conn3.ConnectionString = "Service Name =ORCL;Host=Accounting;
User ID=scott;password=tiger;Min Pool Size=50";
conn.Open();
// conn3 is assigned an existing connection in Pool A that was
// created when Pool A was created for conn1.
Once created, connection pools are not destroyed until the active process ends or the connection lifetime is exceeded. Maintenance of inactive or empty pools involves minimal system overhead.
Using Integrated Windows (NTLM) Authentication with SQL Server
The DataDirect Connect for.NET data provider for Microsoft SQL Server supports Integrated Windows (NTLM) authentication. If the connection string for a SQLServerConnection object sets both the Integrated Security and Pooling Connection options to true, the Domain and User ID information is included with the connection pooling qualification information. In this case, a unique connection string is not the only requirement for creating a pool - instead, a pool is created for each connection string passed by a particular user. This prevents impersonation of one SQL Server user by another through the connection pool.
Because new connection pools will be created for each user who connects, consider carefully the values that you assign for the Min Pool Size and Connection Lifetime connection string options. For example, suppose you have defined a minimum of 100 connections in each pool with a connection lifetime of 60 seconds for each connection. If you have 200 individual users connecting, system resources would be tied up.
Adding Connections to a Pool
A connection pool is created in the process of creating each unique connection string that an application uses. When a pool is created, it is populated with enough connections to satisfy the minimum pool size requirement, set by the Min Pool Size connection string option. If an application is using more connections than Min Pool Size, the data provider allocates additional connections to the pool up to the value of the Max Pool Size connection string option, which sets the maximum number of connections in the pool.
When a Connection object is requested by the application calling the Connection.Open(...) method, the connection is obtained from the pool, if a usable connection is available. A usable connection is defined as a connection not currently in use by another valid Connection object, has a matching distributed transaction context (if applicable).
If the maximum pool size has been reached and no usable connection is available, the request is queued in the data provider. The data provider waits for the value of the Connection Timeout connection string option for a usable connection to return to the application. If this time period expires and no connection has become available, then the data provider returns an error to the application.IMPORTANT: Closing the connection using the Close() method of the Connection object adds or returns the connection to the pool.
Removing Connections from a Pool
A connection is removed from a connection pool and the connection to the database server is terminated when it sits idle (unused) for too long. The length of time that a connection can sit idle in a connection pool before being removed is determined by the Connection Lifetime connection string option. A timestamp is attached to each connection that is released back to a connection pool when the application calls Connection.Close(). The pool manager then periodically checks the pools for connections that have exceeded their Connection Lifetime.
Note, however, that the data provider will always retain the number of connections specified by the Min Pool Size connection option in a connection pool. Setting the Min Pool Size connection option greater than 0 means that many connections in a pool will effectively ignore the Connection Lifetime connection option.
Handling Dead Connections in a Pool
So, what happens when an idle connection loses its physical connection to the database? For example, suppose the database server must be rebooted or the network experiences a temporary interruption. When the application attempts to connect using an existing Connection object from a pool, it could receive errors because the physical connection to the database has been lost.
DataDirect .NET data providers handle this situation transparently to the user. The application will not receive any errors on the Connection.Open() attempt because the data provider simply returns a connection from a connection pool. The first time the Connection object is used to execute a SQL statement (for example, through one of the DataReader execution methods or the DataAdapter.Fill method), the data provider detects that the physical connection to the server has been lost and attempts to reconnect to the server before executing the SQL statement. If the data provider can reconnect to the server, the result of the SQL execution is returned to the application; no errors are returned to the application. The data provider uses the connection failover options, if enabled, when attempting this seamless reconnection. For more information about how DataDirect .NET data providers implement connection failover, refer to the Developer's Forum on the DataDirect Technologies Web site.
Note: Because the data providers can attempt to reconnect to the database server when executing SQL statements, connection errors can be returned to the application on a statement execution. If the data providers cannot reconnect to the server (for example, the server is still down), the execution method throws an error indicating that the reconnect attempt failed, along with specifics about the reason the connection failed.
DataDirect's method of handling dead connections in connection pools allows for the maximum performance out of the connection pooling mechanism. In contrast, some data providers periodically ping the server with a dummy SQL statement while the connections sit idle. Others ping the server when the application requests the use of the connection from the connection pool. Both of these approaches add roundtrips to the database server and ultimately slow down the normal operation of the application.
Handling Distributed Transactions in Oracle
The Pool Manager groups the connections according to the requirement for transactions. If the requesting thread requires a specific transaction context, it must be matched to a connection with the same transaction context, for example, a connection that has been enlisted in distributed transactions.
Because closed connections are returned to the appropriate connection pool, you can close a connection even though a distributed transaction is pending. This means that you can still commit or rollback the distributed transaction until the connection is closed at the server.
In the previous example (see "Creating a Connection Pool"), three new OracleConnection objects were created, but only two connection pools were required to manage them. In the following example, we modify the connection string for conn1 and conn3 to allow enlisting in distributed transactions. If the two connections are enlisted in different distributed transactions, a third connection pool must be added:OracleConnection conn1 = new OracleConnection();
conn1.ConnectionString = "Host=Accounting;
Service Name=ORCL;User ID=scott;password=tiger;
Enlist=true;Min Pool Size=50"; conn1.Open(); // Pool A is created. OracleConnection conn2 = new OracleConnection();
conn2.ConnectionString = "Host=Accounting;
Service Name=ORCL;User ID=Jack;password=quake; Min Pool Size=100";
conn2.Open();
// Pool B is created because the password, User ID,
// and Min Pool Size specified in the connection
// strings differ. OracleConnection conn3 = new OracleConnection();
conn3.ConnectionString = "Host=Accounting;
Service Name=ORCL;User ID=scott;password=tiger; Enlist=true;Min Pool Size=50";
conn3.Open();
// Pool C is created because conn3 enlists in different
// distributed transactions.
Tips on Opening and Closing Connections
Open connections just before they are needed. Opening them earlier than necessary decreases the number of connections available to other users and can increase the demand for resources.
To keep resources available, explicitly Close the connection as soon as it is no longer needed. If you wait for the garbage collector to implicitly clean up connections that go out of scope, the connections will not be returned to the connection pool immediately, tieing up resources that are not actually being used.
Close connections inside a finally block. Code in the finally block always runs, regardless of whether an exception occurs. This guarantees explicit closing of connections. For example:try { DBConn.Open(); // Do some other interesting work } catch (Exception ex) { // Handle exceptions } finally { // Close the connection if (DBConn != null) DBConn.Close(); }
If you are using connection pooling, opening and closing connections is not an expensive operation. Using the Close() method of the data provider's Connection object adds or returns the connection to the connection pool. Remember, however, that closing a connection automatically closes all DataReader objects associated with the connection.
Tracking Connection Pool Performance
All DataDirect Connect for .NET data providers install a set of PerfMon counters that let you tune and debug applications that use the data provider.
The following table describes the PerfMon counters that you can use to tune pooling for your application.
PerfMon Counter
Counter Description
Current # of Connection Pools
Returns the current number of pools associated with the process.
Current # of Pooled Connections
Returns the current number of connections in all pools associated with the process.
Current # of Pooled and Non-Pooled Connections
Returns the current number of pooled and non-pooled connections.
Peak # of Pooled Connections
Returns the highest number of connections in all connection pools since the process started.
Total # of Failed Connects
Returns the total number of attempts to open a connection that failed for any reason since the process started.
Refer to the Microsoft .NET Framework documentation for information about using PerfMon counters.
Improve Performance and Efficiency of ADO.NET Applications
With thoughtful connection management, the performance of .NET applications can be improved. Connection pooling allows you to reduce the number of roundtrips to the server, and allocate connections and system resources more effectively, so that your applications can run more efficiently.
Download an evaluation version of DataDirect Connect for .NET
Buy | 
