Connection Pooling

To get the most out of connection pooling there are some things to keep in mind when developing your application. Learn to stay away from the pitfalls and utilize the full power of connection pooling.
Written by: Bill Rowell

Wikipedia cite "A connection pool is a cache of database connections maintained so that the connections can be reused when future requests to the database are required. Connection pools are used to enhance the performance of executing commands on a database."

Improves performance

Opening and maintaining a database connection for each user, especially requests made to a dynamic database-driven website application, is costly and wastes resources. In connection pooling, after a connection is created, it is placed in the pool and it is used over again so that a new connection does not have to be established. If all the connections are being used, a new connection is made and is added to the pool. Connection pooling also cuts down on the amount of time a user must wait to establish a connection to the database.

How to use

Connection pooling is enabled by default but there are a few things to remember to get the most out of it. Follow these steps and your connection pooling performance boost takes place.

  • Be sure your connections use the same connection string each time. Connection pooling only works if the connection string is the same. If the connection string is different, then a new connection will be opened.
  • Only open a connection when you need it, not before.
  • Close your connection as soon as you are done using it.
  • Don't leave a connection open if it is not being used.
  • Be sure to drop any temporary objects before closing a connection.
  • Be sure to close any user-defined transactions before closing a connection.
  • Don't use application roles if you want to take advantage of connection pooling.

Configuration

Various parameters such as number of minimum connections, maximum connections and idle connections can be set to make sure the connection pool works well according to the environment it is deployed to work in. It is also possible to disable connection pooling which can be useful in debugging scenarios.

Summary

Make sure to follow the basic rules for effective connection pooling and increased application performance.

Connect to

Articles

read all »
Formating Rules for Connection Strings Connection Strings Explained Store Connection String in Web.config The Provider Keyword, ProgID, Versioning and COM CLSID Explained Store and read connection string in appsettings.json SQL Server Data Types Reference