How does the database connection pool affect performance?

Question

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 oriented. I want to know if there is any performance reference, what are the differences in performance when we enable / remove thread pool settings in the ADO.Net connector string setting? Performance I mean both the simultaneous connection that can be supported, and the execution time of a specific SQL command executed on the client side of ADO.Net?

thanks at avdance george

Answer 1

I don't have performance statistics, but maybe a word of warning when using the connection pool: you may have too many small pools, and not one large.

ADO.NET will create a new connection pool for

• each connection string; this is very , if you have two connection strings that differ even in one space or something else, this is considered two separate connection strings and will lead to separate connection pools being created

• for each Windows credential if the "built-in security" setting is used (trusted connection)

So if you have a connection string like

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

one connection pool will be created for each distinguishable user - which is more likely to contradict intuition, but how it is (and cannot be affected / disabled).

See the MSDN docs in the ADO.NET connection pool for more details:

When the connection is open, the connection pool is created based on the exact match algorithm that associates the pool with the connection string in the connection. Each connection pool is associated with a separate connection string. When a new connection is opened, if the connection string is not an exact match for an existing pool, a new pool is created. Connections are combined in a process for each application domain, per connection string and during integration , protection for Windows is used . Connection strings should also be an exact match; the delivered keywords in a different order for the same connection will be combined 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; 

these strings are considered different , and therefore two separate connection pools will be created.

When trying to measure the effect of a connection pool, this is what you need to know!

Mark

Answer 2

The difference in performance will vary greatly from application to application, so there is no solid data on what benefits you should expect.

The best way for you to measure it is by performing stress tests in your application, setting them up with / without combining, and seeing what it looks like.

WCAT is one of the stress tools you can use to download your ASP.NET application.

You can also try a profiler (of which there are many) to control your application to see how it works under voltage.

Some profilers: ANTS , dotTrace

Answer 3

write a small program to access the database. open the database connection without closing it and without looping 100 times.

• Track the time it takes to retrieve data each time.
• Track connections from the side using 'netstat -a'
• with / without association

Answer 4

You say "thread pool", but obviously you are talking about a connection pool, as your header shows.

There is an overhead to create new database connections. This is a resource-intensive operation, so we have an ADO.NET connection pool. Effectively, connections are not closed, but simply returned to the pool, where they remain active and can be reused by other parts of the same code base (inside the AppDomain).

One ADO.NET application pool is created for a unique connection string, so it is worth noting that when using Integrated Security you will lose the benefits of the connection pool, since you will effectively have one connection pool for each authenticated DB user.