Connection Pooling in ASP.NET 2.0


Whenever you are trying to use the Connection Pooling mechanism which is in built in ASP.NET you are trying to improve the scalability and the performance of the application. This feature is not that much useful if your application is not  a  multithreaded. If ASP.NET is involved every second that the connection is idle eats a lot of valuable resources on the server. Theoretically  speaking you need to open the connection as late as possible and close it as soon as possible.

Connection Pooling is automatically enabled for a connection object. If you want to turn off the connection pooling you need to set the “Pooling”  property to false in the connection string. Every connection pool is associated with a distinct connection string. When a new connection  request arrives, and if the connection string does not exactly matches with the already existing connections in the pool a new pool is created. Once a connection pool is created it will not be destroyed unless the process ends. There are two properties that are worth noting here “Max Pool size” and “Min Pool size”. Max Pool size indicates the maximum no of connection objects that can allowed in a pool. The default value is 100. “Min pool size” indicates the minimum no of connection objects that are allowed in the pool. The default value is 0. These properties can be configurable in the web.config. When a connection pool is created multiple connection objects are created until the minimum pool size is reached. When any request for a connection object arrives , it is drawn from the pool in such a way that the connection object should be unused, and a link to the server. If such a connection does not exists the pool tries to create a new connection object. Unless until you call the Close() or Dispose()  methods on the connection objects, they will not be returned to the pool. Its very important that the connection objects are returned to the pool as soon as possible.It is always recommended that you have to use try, catch,finally when we are using connections as shown below.

SqlConnection myconn = new  SqlConnection (connectionstring);

try

{

myconn .Open();

//Do something

}

catch

{

//catch the exceptions

}

finally

{

myconn.Close();

}

Alternatively you can use the using statement as follows

using(SqlConnection myconn = new SqlConnection(connectionString))

{

//Do something;

//catch the exceptions;

}

This is almost equivalent to try,catch and finally blocks.

How to detect connection leaks?

In your application if you are getting an invalid operation exception or any timeout exception it is a good symptom of connection leaking. There are a couple of things that you can do to reduce the frequency of this type of exceptions.

1.Configure connection pooling – If it is disabled a new connection object is requested each time which will reduce the performance of the application.

2. Don’t reduce the ConnectionLifetime property – “ConnectionLifetime” property sets the maximum duration in seconds of the connection object in the pool.Shorter duration of the connectionlifetime property means renewal of connection objects more frequently which leads to performance degradation.

3. Don’t increase the Connection Timeout-This indicates the time for a pool to wait until it gets a valid connection object. Whatever the timeout value ASP.NET aborts that thread after 3 minutes.increases this value reduces the performance .

4.Don’t increase the pool size – If you set this to a very high value you may not get the time out exception but you are forcing to select from a large number of connection objects.

It is extremely important that you need to write a code that will guarantee the closing of connection object. But in some cases it is not the case. Take the following example…

SqlConnection myconn = new SqlConnection(connectionString);

myconn.Open();

SqlCommand mycommand  = new SqlCommand(“SELECT * FROM Employee”, myconn)

myCommand.ExecuteNonQuery();

myconn.Close();

If the command throws an exception the Close() method will not be called and the connection object will not be returned to the pool. So to avoid this we can write the code as follows

using(SqlConnection myconn = new SqlConnection(connectionString);

{

myconn.Open();

SqlCommand mycommand  = new SqlCommand(“SELECT * FROM Employee”, myconn)

myCommand.ExecuteNonQuery();

myconn.Close();

}

This code ensures that Dispose is always called on the object being used. This is the only way to avoid connection leaking.

There is on more scenario that is worth mentioning here. Suppose lets assume your ASP.NET application creates a connection pool and the server goes down or gets restarted. Now all the connection objects in the pool are invalid. What will happen if the page requests a new connection object? This will raise an exception because the connection object is not valid.

The only solution for this type of situation is flushing the connection pool.Clearpool() static method of the SqlConnection class will do the trick and clear all the connection objects in the pool.

Advertisements
This entry was posted in ASP.NET. Bookmark the permalink.

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