Connection Pooling — ADO.net

Most of the software engineers have a vague idea about connection pooling and what are the usage of it. This article will demonstrate following sections in regard to connection pooling in ADO.net.

• What is connection pooling
• The need of it
• How to achieve
• Attributes
• Drawbacks
• Conclusion

What is Connection Pooling

Connection pooling is a concept of maintaining a list of DB connections which can be used to provide an active connection when requested. This will not only help to offer a connection but also reduce the number of connection established since an already opened connection can be re-used.
When all connections are used, a new connection will be created and added to the pool. In addition, having enabled a connection pool, largely reduce the waiting time when a request made to establish a connection.

The need of it

As most developers experienced, establishing a connection to the DB server is the single most expensive and resource consuming task. To lay stress on the overhead of when opening up a connection, consider the following scenario.
10,000 people send requests to pull data from a web service over the period of 1 minute. And one may think of opening up and activating 10,000 connections to each user and negotiating with the server. This will probably burn down the server.

If connection pool was set up to accommodate few requests (Let’s say 50), then only 200 connections are created for entire 10,000 requests. You can think of the amount of time and resources saved.

In addition, most of the RDBMS allow finite number of connections to negotiate with it and due to the high number of communications with RDBMS and time takes to process each request, connections can hold lock on data.

Therefore, connection pool must be set up accordingly based on the significance of the tasks and the number of users access a particular service.

OK, lets’s discuss how to set up a connection pool in below section.

How to achieve

A connection string in the web.config with connection pooling.

"Connectionstring":"Data Source=YourDbServer;Initial Catalog=DbName;Integrated Security=True;Pooling=True;Min Pool Size=10;Max Pool Size=150"

Note:
Each connection pool is created for every unique connection string, even the SQL instance differs but the same credentials. For an e-commerce application it is usually recommended to have 1–3 connection pools.

connection pooling is enabled by default and can be customized with following properties according to your requirements.

Default values of connection pool properties

Now let’s see what this each property does.

Attributes

• Enlist - The current connection object will be automatically enlisted in the current transaction scope, if it is active. You can disable the auto enlistment by setting false to this property Enlist = false . Check this for a such scenario.
• Load Balance Timeout - The minimum amount of time (in seconds) for a particular connection to live in this pool.
  0 - No specific time and it is kept open.
  x - A value of positive integer in seconds, it will be destroyed when reached this number.
• Max Pool Size - The maximum number of connections accommodate in a single pool. When this number reached, no additional connections are created from the pool. Please note that, this determines the performance of the application and it can be deteriorated if not set correctly.
• Min Pool Size - The minimum number of connections allowed in the pool.
  0 - No additional connection are created in addition to the existing connection.
  x- The number of connections created in the pool at the beginning and even if a particular connection object exceeds the LoadBalance Timeout,the connection may retain depend on the value being set here.
• Pool Blocking Period- Defines the blocking period behavior for a connection pool. When a pooling is enabled and fail to connect to DB server, an exception is thrown and subsequent connection attempts will fail for the next five seconds for one minute,”the blocking period”.However, this behavior has been removed for Azure connections.
  
  AlwaysBlock - 1 - Blocking period on for SQL server including Azure servers.
  Auto- 0- Blocking period OFF for Azure SQL servers but ON for all other SQL servers.
  NeverBlock- 2 - Blocking period OFF for all SQL servers including Azure SQL servers.
• Pooling - When set to true, the connection object is retrieved from the particular pool or if necessary a connection is created and added to the pool.

Drawbacks

• The hassle of configuration- Though ADO.net enables pooling by default, it takes a significant amount of effort to configure it properly based on the number of concurrent users, maximum number of pools,max pool size,load balance time out etc. When pooling options are not set up correctly, it can downgrade the performance of the application.
• Pool overflow- When a pool is reached its maximum number of connections,exceptions will be thrown due to the overflowing of the pool. This can be happened due to two reasons.
  1. Connections may not be closed/disposed properly once they are drawn from the pool and used.
  2. The actual number of users exceed the maximum pool size. Therefore, another round of proper configuration required.

Conclusion

ADO.net connection pooling increases the performance of the application by opening and re-using existing connections. However, one must be configured this carefully by analyzing the requirement of the production environment. In addition, it is always advisable to actively monitor the behavior of the pool.Click this for more.