rutledgj
asked on
ado.net connection pooling question
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
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
ASKER
So are you saying that one connection created can result in multiple connections through connection pooling?
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.
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.
ASKER
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.
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
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
ASKER
I guess this is what doesn't make sense in your statement.
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks. Just one comment. Isn't connection pooling used by default in ado.net? Didn't know you had to turn it on.
Yes, it is on by default, but you can turn it off if you want.
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