Friday, May 4, 2007

Improving Performance with Connection Pooling

Opening a connection is a database-intensive task. It can be one of the slowest operations that you perform in an ASP.NET page. Furthermore, a database has a limited supply of connections, and each connection requires a certain amount of memory overhead (approximately 40 kilobytes per connection).

If you plan to have hundreds of users hitting your Web site simultaneously, the process of opening a database connection for each user can have a severe impact on the performance of your Web site.

Fortunately, you can safely ignore these bad warnings if you take advantage of connection pooling. When database connections are pooled, a set of connections is kept open so that they can be shared among multiple users. When you request a new connection, an active connection is removed from the pool. When you close the connection, the connection is placed back in the pool.

Connection pooling is enabled for both OleDb and SqlClient connections by default.

To take advantage of connection pooling, you must be careful to do two things in your ASP.NET pages. First, you must be careful to use the same exact connection string whenever you open a database connection. Only those connections opened with the same connection string can be placed in the same connection pool. For this reason you should place your connection string in the web.config file and retrieve it from this file whenever you need to open a connection

To take advantage of connection pooling in your ASP.NET pages, you also must be careful to explicitly close whatever connection you open as quickly as possible. If you do not explicitly close a connection with the Close() method, the connection is never added back to the connection pool.

connection pooling options that you can add to the SQL Server connection string:

  • Connection Lifetime— Destroys a connection after a certain number of seconds. The default value is 0, which indicates that connections should never be destroyed.
  • Connection Reset— Indicates whether connections should be reset when they are returned to the pool. The default value is true.
  • Enlist— Indicates whether a connection should be automatically enlisted in the current transaction context. The default value is true.
  • Max Pool Size— The maximum number of connections allowed in a single connection pool. The default value is 100.
  • Min Pool Size— The minimum number of connections allowed in a single connection pool. The default value is 0.
  • Pooling— Determines whether connection pooling is enabled or disabled. The default value is true.

Most Recent Post

Most Recent Ado.net FAQ

Most Recent .Net Framework FAQ

Most Recent Configuration Files FAQ

Daily Quote, Inspiration, Motivation and More

Subscribe Blog via Email

Enter your email address: