Understanding Connection Pooling in .NET
page 1 of 5
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): 167458/ 180

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.


View Entire Article

User Comments

Title: Author's Response   
Name: Joydip
Date: 12/29/2006 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: 12/28/2006 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: 12/28/2006 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: 12/28/2006 2:12:55 PM
Comment:
Keep it up!
Title: Too bad   
Name: Haissam Abdul Malak
Date: 12/27/2006 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: 12/27/2006 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: 12/27/2006 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: 12/27/2006 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: 12/26/2006 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: 12/26/2006 10:18:28 PM
Comment:
Thanks Joydip
Title: Give me a break, Bob and Zen   
Name: Francis
Date: 12/26/2006 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: 12/26/2006 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: 12/26/2006 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: 12/26/2006 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: 12/26/2006 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: 12/26/2006 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: 12/26/2006 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: 12/26/2006 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: 12/25/2006 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: 12/22/2006 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: 12/21/2006 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-2014 ASPAlliance.com  |  Page Processed at 11/1/2014 6:23:38 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search