Connection Pooling

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."

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.

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.

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.

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