Understanding Connection Pooling in .NET
page 3 of 5
by Joydip Kanjilal
Feedback
Average Rating: 
Views (Total / Last 10 Days): 75096/ 112

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


View Entire Article

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 5:14:10 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search