Understanding Connection Pooling in .NET
 
Published: 18 Dec 2006
Abstract
Connection Pooling can increase the database performance to a huge extent. This article discusses what Connection Pooling actually is, how it can boost the performance of database access and how we can create a Connection Pool in .NET and add and remove connections to it.
by Joydip Kanjilal
Feedback
Average Rating: 
Views (Total / Last 10 Days): 75099/ 116

Introduction

Connecting to the database is resource intensive and a relatively slow operation in an application but the most crucial of them all. A Connection Pool is a container of open and reusable connections. A Connection Pool is released from the memory when the last connection to the database is closed. The basic advantage of using Connection Pooling is an improvement of performance and scalability while the main disadvantage is that one or more database connections, even if they are currently not being used, are kept open. The Data Providers in ADO.NET have Connection Pooling turned on by default; if you need to turn it off, specify Pooling = false in the connection string being used. Connection Pooling gives you an idle, open, reusable connection instead of opening a new one every time a connection request to the database is made. When the connection is closed or disposed, it is returned to the pool and remains idle until a request for a new connection comes in. If we use Connection Pooling efficiently, opening and closing of connections to the database becomes less resource expensive. This article discusses what Connection Pooling is all about and how Connection Pooling can be used efficiently to boost the performance and scalability of applications.

How does a Connection Pool work?

Connection pools are actually containers that contain open and reusable connections. Multiple pools can exist in the same application domain at the same point in time, but Connection Pools cannot be shared across application domains. Note that one pool is created per unique connection string. A Connection Pool is created the first time a request for a connection to the database comes in with a unique connection string. Note that if another request comes in with a different connection string for the database, another Connection Pool would be created. Hence, we have one Connection Pool per connection string and not per database. The following code listings below illustrate this.

Listing 1

// A new pool is created.
SqlConnection sqlConnection = new SqlConnection();
sqlConnection.ConnectionString = 
"Server=localhost;Database=test;User ID=joydip;Password=joydip;Trusted_Connection=False";
sqlConnection.Open();      

Listing 2

// A new pool is created as the connection strings differ.
SqlConnection conn = new SqlConnection();
sqlConnection.ConnectionString = 
"Server=localhost;Database=test;User ID=test;Password=test;Trusted_Connection=False";
sqlConnection.Open();   

Listing 3

// The connection string is the same as in Listing 1 so no new pool is created.
SqlConnection conn = new SqlConnection();
sqlConnection.ConnectionString = 
"Server=localhost;Database=test;User ID=joydip;Password=joydip;Trusted_Connection=False";
sqlConnection.Open();      

When a request for a new connection comes in the connection is served from the Connection Pool without creating a new one, i.e., the connections are re-used without creating new ones.  Therefore, it improves the performance and scalability of your applications. When your application closes an open connection, it is returned to the pool where it waits until a reconnect time out period expires. This is the period within which it waits to connect to the same database using the same credentials. If none comes in within this period, the connection to the database is closed and the connection instance is removed from the pool.

When a new Connection Pool is created, the connection objects are placed in the pool and the pool and the connections contained in it are made available for immediate use. The pool can house connections up to the maximum limit as specified in the connection string that was used to connect to the database. Connections are removed from the pool when they remain inactive for a long period of time or have outlived its specified life time or server connectivity.

A Connection Pool is maintained internally by the Connection Pool Manager. When a request for a subsequent connection comes in, the Connection Pool Manager searches the pool for the availability of a free connection and returns it to the application if one is available. The following points elaborate how the Connection Pool Manager works- the operations that it performs when a request for a new connection comes to it.

·         If any unused connection is available, it returns one.

·         If all connections are used up, a new connection is created and added to the pool.

·         If the number of connections reaches the maximum number of connections in the pool, the requests are queued until a connection becomes free for reuse.

Connection Pooling is controlled and the parameters passed to a connection string that basically comprises the following:

·         Connect Timeout

·         Min Pool Size

·         Max Pool Size

·         Pooling

In order to use Connection Pooling efficiently, remember to close the connections once you are done with it so that it returns to the pool.

Improving Connection Pooling Performance

We should always open the connections late and release them early; in other words, immediately after we are done using it. Connections should be opened only at the time when they are actually required.  The connections should not be acquired prior to its usage as it would decrease the number of available connections in the Connection Pool and, hence, have detrimental effects to the operation of the Connection Pool and the application's performance. The connections should be explicitly released immediately when we are done using it. This would facilitate better Connection Pooling as the connection would be returned to the pool and be available for reuse. The following snippet of code shows how we should open and close connections in our applications efficiently.

Listing 4

SqlConnection sqlConnection = new SqlConnection(connectionString);
try
{
  sqlConnection.Open();
  //Some Code
}
 
finally
{
  sqlConnection.Close();
}

The code in Listing 4 can be simplified further by using the "using" keyword as shown in the code snippet below.

Listing 5

using(SqlConnection sqlConnection = new SqlConnection(connectionString))
{
  sqlConnection.Open();
  //Some Code
}

Note that using the "using" keyword as shown in Listing V above, generates the try-finally block implicitly.

The following points illustrate the measures that can be taken for better utilization of the Connection Pool.

·         Always open connections when needed and close it immediately when you are done using it.

·         Close the user-defined transactions before closing the related connections.

·         Ensure that there is at least one connection open in the pool to maintain the Connection Pool.

·         Avoid using connection pooling if integrated security is being used.

Connection Pooling can be monitored in one of the following ways:

·         Executing the sp_who or sp_who2 stored procedures

·         Using the SQL Server Profiler

·         Using the Performance Monitor and Performance Counters

References

Conclusion

A Connection Pool, a container of connection objects, remains active as long as there are active or open connections in it. When a request for new connection comes in with a connection string, a new Connection Pool is allocated. We can improve the performance and scalability of our applications by using the same connection string in our applications. However, we should use Connection Pooling appropriately as using it inappropriately might have negative effects to the overall performance of our applications. MSDN says, "Connection pooling is a powerful functionality that can improve your applications' performance. But if you aren't a good lifeguard, your connection pools can become a detriment instead of a benefit." This article has discussed Connection Pooling and how we can use it efficiently for improving the performance and scalability of our applications.



User Comments

Title: Author's Response   
Name: Joydip
Date: 2006-12-29 12:56:23 AM
Comment:
Hi Ritesh & Neha,

Thanks for your queries.

When the application closes the database connection by making a call to the Close method on the connection instance, the connection is returned to the pool. There it would wait for a specific amount of time, known as the reconnect time-out period, for a new open request from your application to the same database instance using the same security context used earlier to connect to this database. If no connection request comes in before the expiry of this "reconnect time-out period", the connection with the database instance is closed and the entry deleted from the connection pool.

Hope this helps.

-Joydip
Title: about reconnect time   
Name: Ritesh
Date: 2006-12-28 11:09:21 PM
Comment:
Hi Joydeep two similar queries are posted one by my self and other by Neha . I hope you could get some time to answer this
Title: Clarification Req.   
Name: Neha
Date: 2006-12-28 2:22:06 PM
Comment:
"it is returned to the pool where it waits until a reconnect time out period expires.." Here what do u mean by
'reconnect time out period' ? Can it be set from application?Is it in terms of minute or second? We offen see 'connection time out' message in application,'connection time out' and 'reconnect time out period' both are same?
Title: Good Work   
Name: Laloo
Date: 2006-12-28 2:12:55 PM
Comment:
Keep it up!
Title: Too bad   
Name: Haissam Abdul Malak
Date: 2006-12-27 6:09:13 PM
Comment:
Dont worry about the critizes, it will only make u trust yourself more coz i see jealousy in behind our two friends acts.. Keep up the good work and provide more information to serve this community
Title: Humm?   
Name: Nate
Date: 2006-12-27 12:17:38 PM
Comment:
Bob and Zen may be the same person.
This article was a decent intro for newbies which is his target audience. Most of us may already know about this but it is a source of published information for the community.
I do not understand why Bob is so hostile.
Title: about reconnect time   
Name: Ritesh
Date: 2006-12-27 6:29:41 AM
Comment:
You mention some thing about reconnect time where do we mention it. And also can you explain if my pool size is 20 and I am opening 15th connections so there is scope of 6 connections.
Out of 14 connections some them already released means at this moment they are free so will my 15th connection will create new instance and assigned to pool or will it used some unused connection?.;

I hope you are getting what I mean to say
Title: Trusted connection   
Name: Jeff
Date: 2006-12-27 2:21:00 AM
Comment:
What might be the reason connection pooling should be avoided on a trusted connection? Aren't we encouraged to use trusted connection whenever possible? Given the two, which should we strive for then?

Thanks,
Jeff
Title: Mr.   
Name: Maheswar
Date: 2006-12-26 11:58:45 PM
Comment:
Hi Joydip

i have a small doubt
using(SqlConnection sqlConnection = new SqlConnection(connectionString))
{
sqlConnection.Open();
//Some Code
}

in this syntax if i get any Exception before executing the last braces It will close the connection or we need to take care for this?

Thanks
Maheswar
Title: Mr   
Name: Murry Gammash
Date: 2006-12-26 10:18:28 PM
Comment:
Thanks Joydip
Title: Give me a break, Bob and Zen   
Name: Francis
Date: 2006-12-26 8:03:21 PM
Comment:
Don't you guys have better things to do with your time than criticize articles? I concur with others - while this article may be 'beneath you', it certainly can benefit many other developers. If you have something constructive to share with the author, then please share it.

By the way, 'Bob Smith', your C# may or may not be impeccable, but your English grammar is lousy: Two of your three sentences are laden with very obvious incorrect grammar. My constructive criticism for you is to go back to high school for English lessons. Joydip, by the way, used impeccable English in his article.

And we must mention that "Intellectual Data Systems" comes up with only one hit on Google, which clearly has nothing to do with development. I highly doubt you are even a "Senior Architect" at all.

Good job Joydip, keep the articles coming.
Title: Good 1 for new comers   
Name: CKV
Date: 2006-12-26 3:38:22 PM
Comment:
Simple and clear.

To Bob and zen,
if you think this article is missing something, why don't you contact the author and advice him instead of making a nice comment.

If we (developers) make comment like that then no body will like to put an article and also will not like to serve community.

Peace.
Title: Give us a break.   
Name: Tom J
Date: 2006-12-26 11:29:57 AM
Comment:
To Bob and Zen
No need to make comment like that. Lots of people will benefit from a article even that it contains, based on your standard, little information.
Title: Author's Response   
Name: Joydip
Date: 2006-12-26 10:28:33 AM
Comment:
Hi Bob & Zen,

Thanks for the response. I do not feel that more coverage than what has been provided here on Connection Pooling is required for an application software developer who is new to .NET; I disagree that this is an incomplete article. There are plenty of code samples and discussions even more than what is necesary for a new comer to .NET

--Joydip
Title: Mr.   
Name: Zen
Date: 2006-12-26 9:10:10 AM
Comment:
@Bob Smith,

I agree with you. It is disappointing when people publish incomplete articles like this one.

Zen
Title: Author's Response   
Name: Joydip
Date: 2006-12-26 5:48:52 AM
Comment:
Hi Ali,

Thanks for your response.

When the end of the using block would be encountered, the Dispose() method will be immediately called on the instance. Note that when the Dispose() method is called on this connection instance, it checks to see if the connection is opened; if open it would close it implicitely prior to disposing off the instance.

--Joydip
Title: Closing connection   
Name: ali
Date: 2006-12-26 4:40:57 AM
Comment:
Hi,
Just want to ask from the below code
using(SqlConnection sqlConnection = new SqlConnection(connectionString))
{
sqlConnection.Open();
//Some Code
}

How does the connection is closed? like in
finally
{
sqlConnection.Close();
}

Thanks
Title: Imp   
Name: Author's Response
Date: 2006-12-26 1:59:09 AM
Comment:
Hi Bob,

Please let me know what a Connection Pool is then. Please browse through this link:--

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconconnectionpoolingforsqlservernetdataprovider.asp

Expecting your response.

-Joydip
Title: Mr.   
Name: Bob Smith
Date: 2006-12-25 12:39:22 AM
Comment:
Is this what do you call as the Connection Pool. Shame of you.


Be sure before publishing this kind of articles. It is very sad to see this kind of article in ASP Alliance.

Bob Smith,

Senior Architect,
Intellectual Data Systems.
Title: Pooling   
Name: Rudresh R Kaddipudi
Date: 2006-12-22 4:00:16 AM
Comment:
Hi Shawn de Wet,

http://msdn2.microsoft.com/en-us/library/8xx3tyca.aspx

All database connection requests raising at Host(where the connection requests are made) are trapped by the ADO.Net connection pool manager and served if one available in the pool. The pooling is just to avoid roundtrip information exchange to establish a database connection.
Title: Where does the pool live?   
Name: Shawn de Wet
Date: 2006-12-21 3:58:59 AM
Comment:
Where does the connection pool reside in memory...on the client or on the SQL Server? If I have a deployed application running on 20 client machines (all using the same connection string), does each client maintain its own connection pool, or does a single pool liver on the SQL Server for all clients to use?

Product Spotlight
Product Spotlight 





Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2022 ASPAlliance.com  |  Page Processed at 2022-01-20 6:17:35 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search