views:

409

answers:

4

Hello everyone,

I am using .Net 2.0 + SQL Server 2005 Enterprise + VSTS 2008 + C# + ADO.Net to develop ASP.Net Web application. The ASP.Net Web application is database centric/driven. I want to know whether there are any performance reference data about what are the differences of performance when we turn on/off thread pool setting in ADO.Net connecting string setting? Performance I mean both the concurrent connection which could be supported and the execution time of a specific SQL Command executed from ADO.Net client side?

thanks in avdance, George

+2  A: 

The performance difference is going to vary widely from application to application, so there is no hard data on what kind of gains you should expect.

The best course of action for you to take it to measure it by doing stress tests on your app with it configured with/without pooling and see how it fairs.

WCAT is one kind of stress tool you can use to load your ASP.NET application.

You can also try a profiler (of which there are many) to monitor your application to see how it performs under the stress.

Some Profilers: ANTS, dotTrace

Joseph
The profilers are you recommended are really cool. But not free. :-)Any Microsoft provided tool (I have MSDN license so I think I can use any non-free tool from Microsoft, which the license already covered) or free tool?
George2
+1  A: 

write a small progeam to access the database. open the database connection without close it and loop 100 times.

  1. Monitor the time you need to get the data each time.
  2. Monitor the sever side connections using 'netstat -a'
  3. with/without pooling
Henry Gao
Can you describe in more details why we need step 2, and you want step 2 to prove what?
George2
Step 2 is used to monitor how many connections you need to have to access database in you server side. My experience is if you have pooling, 2 or three connectiona are enough, but if you don't, you may have a lot depending how many concurrent users
Henry Gao
+2  A: 

I don't have any performance stats at hand, but maybe a word of warning when using connection pooling: you might end up with too many small pools, instead of one large one.

ADO.NET will create a new connection pool for

  • each connection string; it is very picky about this, too - if you have two connection strings that are different by even just a single space or something, those are considered two separate connection strings, and will lead to separate connection pools being created

  • for each Windows credentials if using the "integrated security" (trusted connection) setting

So if you have a connection string something like

server=MyDBServer;database=MyDatabase;integrated security=SSPI;

one connection pool for each distinguishable user will be created - that's rather counter-intuitive, but that's the way it is (and it cannot be influenced / turned off).

Check out the MSDN docs on ADO.NET connection pooling for details:

When a connection is first opened, a connection pool is created based on an exact matching algorithm that associates the pool with the connection string in the connection. Each connection pool is associated with a distinct connection string. When a new connection is opened, if the connection string is not an exact match to an existing pool, a new pool is created. Connections are pooled per process, per application domain, per connection string and when integrated *security is used, per Windows* identity. Connection strings must also be an exact match; keywords supplied in a different order for the same connection will be pooled separately.

Also, if you have these two connection strings:

server=MyDBServer;database=MyDatabase;user id=tom;pwd=top$secret

and

server=MyDBServer;database=MyDatabase;user id=tom; pwd=top$secret;

those are considered different connection strings, and thus two separate connection pools will be created.

When attempting to measure the effect of connection pooling, this is something to be aware of!

Marc

marc_s
Thanks for your advice, Marc!
George2
+1  A: 

You say 'thread pool', but obviously are talking about connection pool as your title shows.

There is a performance overhead for creating new database connections. It's a resource intensive operation, which is why we have ADO.NET connection pooling. Effectively connections are not closed, but merely returned to the pool where they stay active and can be reused by other parts of the same code base (inside the AppDomain).

One ADO.NET application pool is created PER unique connection string, therefore it is worth noting that when you use Integrated Security, you will lose the benefits of connection pooling as you will effectively have one connection pool per DB authenticated user.

Wim Hollebrandse
"You say 'thread pool', but obviously are talking about connection pool as your title shows." -- thanks for pointing out. I have corrected my post title. :-)
George2
My question is not about how database connection pool works, but any general data for reference about the performance differences compared with using and not using database connection pool. Any document to referece?
George2