Improve Your Technology

Just another blog for techology

Connection Pooling in .NET Applications

Connection Pooling in .NET Applications

Connecting to a database is the single slowest operation performed by a data-centric application. This document describes how reusing pooled connections, instead of creating new connections, can improve .NET application performance.

 

You can control connection pooling behavior by using the connection string options set for your ADO.NET data provider. For example, connection string options can define the following settings for the DataDirect Connect® for ADO.NET data providers:

 

• Number of connection pools (Max Number of Pools)
• Maximum connection pool size (Max Pool Size)
• Minimum number of connections in a connection pool (Min Pool Size)
• Number of seconds to keep connections in a connection pool (Connection Lifetime)
• Whether to enable Windows Integration authentication for the SQL Server data provider (Integrated Security)

 

This document also describes the performance advantages of DataDirect’s technique of handling dead connections in a connection pool, as well as tips on opening and closing connections. In addition, C# code examples illustrate how to create connection pools and how to handle distributed transactions when using a connection pool.

 

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 DbConnection 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.

DbProviderFactory Factory =
DbProviderFactories.GetFactory(“DDTek.Oracle”);
DbConnection Conn1 = Factory.CreateConnection();
Conn1.ConnectionString =
“Host=Accounting;Port=1521;User ID=scott;Password=tiger; ” +
“Service Name=ORCL;Min Pool Size=50”;

Conn1.Open();
// Pool A is created and filled with connections to the
// minimum pool size

DbConnection Conn2 = Factory.CreateConnection();
Conn2.ConnectionString =
“Host=Accounting;Port=1521;User ID=Jack;Password=quake; ” +
“Service Name=ORCL;Min Pool Size=100”;

Conn2.Open();
// Pool B is created because the connections strings differ

DbConnection Conn3 = Factory.CreateConnection();
Conn3.ConnectionString =
“Host=Accounting;Port=1521;User ID=scott;Password=tiger; ” +
“Service Name=ORCL;Min Pool Size=50”;

Conn3.Open();
// Conn3 is assigned an existing connection that was created in
// Pool A when the pool 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.

 

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, which is 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 DbConnection object is requested by the application calling the DbConnection.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 DbConnection object that 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 becomes available, the data provider returns an error to the application.

IMPORTANT: Closing the connection using the Close() method of the DbConnection 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 remains idle (unused) for too long, or when a new connection that has a matching connection string is initiated by the application (DbConnection.Open() is called). The length of time that a connection can remain idle in a connection pool before being removed is determined by the Load Balance Timeout connection string option.

 

Before returning a connection from the connection pool to an application, the Pool Manager checks to see if the connection has been closed at the server. If the connection is no longer valid, the Pool Manager discards it and returns another connection from the pool, if one is available and valid.

 

The Clear Pool and Clear All Pools methods of the Connection object, introduced in the .NET Framework 2.0, clear connection pools. ClearPool clears the connection pool associated with a specific connection. In contrast, ClearAllPools clears all of the connection pools used by the data provider. Connections that are in use when the method is called are discarded when they are closed.

 

Note, however, that the data provider always retains the number of connections specified by the Min Pool Size connection option in a connection pool. So, setting the Min Pool Size connection option greater than 0 means that many connections in a pool effectively will ignore the Load Balance Timeout 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 is rebooted or the network experiences a temporary interruption. When the application attempts to connect using an existing DbConnection object from a pool, it could receive errors because the physical connection to the database has been lost.

 

DataDirect ADO.NET data providers handle this situation transparently to the user. The application does not receive any errors on the DbConnection.Open() attempt because the data provider simply returns a connection from a connection pool. The first time the DbConnection 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 ADO.NET data providers implement connection failover, refer to ” Failover Support in DataDirect Connect for ADO.NET Data Providers.”

 

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 of the connection pooling mechanism. In contrast, some data providers periodically ping the server with a dummy SQL statement while the connections remain 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

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.

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 are not 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
{
Conn1.Open();
// Do some other interesting work
}
catch (Exception ex)
{
// Handle exceptions
}
finally
{
// Close the connection
if (Conn1 != null)
Conn1.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 ADO.NET data providers install a set of Performance Monitor (PerfMon) counters that return information that lets you tune your applications for performance.

The following table describes the PerfMon counters that you can use to tune pooling for your application.

 

PerfMon Counter & Counter Description

1. Current # of Connection Pools: Returns the current number of pools associated with the process.
2. Current # of Pooled Connections: Returns the current number of connections in all pools associated with the process.
3. Current # of Pooled and Non-Pooled Connections: Returns the current number of pooled and non-pooled connections.
4. Peak # of Pooled Connections: Returns the highest number of connections in all connection pools since the process started.
5. 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.

Advertisements

February 7, 2009 - Posted by | Connection Pooling in .NET, Technology | ,

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: