Solved

ado.net connection pooling question

Posted on 2011-03-21
9
306 Views
Last Modified: 2012-06-27
I'm trying to understand connection pooling as it relates to a multi-threaded application.
If I have 5 threads continuously running and they all create a sql connection, shouldn't I have 5 distinct connections to the db?  Using the sql trace I don't see but 2.

If I up my threads to 10 the connection count doesn't change.  Inside the thread, the connection is opened and closed as it is used.

I understand the connection pooling just returns it back to the pool but I would still think I would have more than 2 connections at any given time. The threads run very quickly but upping the thread count doesn't seem to improve performance.

Rut
0
Comment
Question by:rutledgj
  • 5
  • 4
9 Comments
 
LVL 13

Expert Comment

by:gamarrojgq
ID: 35184419
Hi,

Connection pooling is a useful way to reuse connections for multiple requests, rather than paying the overhead of opening and closing a connection for each request. It's done implicitly, but you get one pool per unique connection string. If you're generating connection strings dynamically, make sure the strings are identical each time so pooling occurs.

Hope that helps
0
 

Author Comment

by:rutledgj
ID: 35185549
So are you saying that one connection created can result in multiple connections through connection pooling?
0
 
LVL 13

Expert Comment

by:gamarrojgq
ID: 35185614
Well, connection pooling use active connections of the pool for consecutive requests, rather than creating a new connection each time.

So if your threads have the same  Connection String, the first one will Create and Open a DB Connection and the second, third, etc will use the same connection instead of create another a new one.
0
 

Author Comment

by:rutledgj
ID: 35185772
so the second (and subsequent) threads will have to wait until the first thread finishes with the connection until it can use it? The connection it a single object created as a public shared variable.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 13

Expert Comment

by:gamarrojgq
ID: 35186044
Ok, the connection pool will work this way when a thread needs a DB Connection:

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.

So, the second and subsequent threads will have to wait ONLY if the connection created by the First Thread is still in use, if not, they will use it rigth away
0
 

Author Comment

by:rutledgj
ID: 35188757
I guess this is what doesn't make sense in your statement.

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 my application opens one connection for all threads to use and thread #1 is using it, does the connection pool create more connections for the remaining threads or do they have to wait for thread #1 to finish?
0
 
LVL 13

Accepted Solution

by:
gamarrojgq earned 250 total points
ID: 35189227
ok, that would depend on the pool maxpoolsize property, if you set this property > 1, if the Thread #1 is using the connection, the connection pool will create another connection for the memaining threads, but if you are not using connection pool or the maxpoolsize = 1, then the other threads will have to wait

Example

Thread 1 Request a DB Connection    
       The connection Pool Creates a New One
Thread 1 Use it and Close it

Thread 2 Request a DB Connection
       The connection Pool reuse the connection Created by Thread1
Thread 2 START using the connection

Thread 3 Request a DB Connectino
       Since the Thread 2 is still using the connection Created by Thread1 it Creates a Second
Thread 3 Use the new Connection an Close It

Thread 2 Close the connection Created by Thread1

And this cycle repeats according to the Threads DB Connections requests



0
 

Author Closing Comment

by:rutledgj
ID: 35189425
Thanks. Just one comment. Isn't connection pooling used by default in ado.net? Didn't know you had to turn it on.
0
 
LVL 13

Expert Comment

by:gamarrojgq
ID: 35190133
Yes, it is on by default, but you can turn it off if you want.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

28 Experts available now in Live!

Get 1:1 Help Now