Abstract
The process of establishing a database connection can be time consuming depending upon network connectivity. Connection pooling is a viable option if the network is an issue, and database server resources are available. I'll begin by discussing connection pooling and examining how you may use it in your .NET applications.
Introduction
Connecting to the database is resource intensive and a relatively slow operation in an application.
There are several steps involved with establishing a database connection.
· First, you will establish a connection to the database server over the network.
· Next, the connection string is parsed and the user authenticated.
· Finally, the connection is established, and operations may be performed.
Connection pooling allows an application to maintain ownership of a database connection.
A Connection Pool is a container of Open & Reusable Connections. A Connection Pool is released from the memory when the last connection to the database is closed. The basic advantage of using Connection Pooling is an improvement of performance and scalability while the main disadvantage is that one or more database connections, even if they are currently not being used, are kept open.
Connection Pooling gives you an idle, open, reusable connection Instead Of Opening A New One Every Time A Connection Request to the database is made. When the connection is closed or disposed, It Is Returned To The Pool And Remains Idle until a request for a new connection comes in. If we use Connection Pooling efficiently, opening and closing of connections to the database becomes less resource expensive. This article discusses what Connection Pooling is all about and how Connection Pooling can be used efficiently to boost the performance and scalability of applications.
An interesting note: Connection pooling is utilized (by default) unless otherwise specified.
How does a Connection Pool work?
Connection pools are actually containers that contain open and reusable connections. Multiple pools can exist in the same application domain at the same point in time, but Connection Pools cannot be shared across application domains. Note that one pool is created per unique connection string. A Connection Pool is created the first time a request for a connection to the database comes in with a unique connection string. Note that if another request comes in with a different connection string for the database, another Connection Pool would be created. Hence, we have one Connection Pool per connection string and not per database.
The pool can house connections up to the maximum limit as specified in the connection string that was used to connect to the database.
When a request for a new connection is served from the Connection Pool, it is done without creating a new one, i.e., the connections are re-used without creating new ones. Therefore, it improves the performance and scalability of your applications. When your application closes an open connection, it is returned to the pool where it waits until a Reconnect Time Out Period Expires. This is the period within which it waits to connect to the same database using the same credentials. If none comes in within this period, the connection to the database is closed and the Connection Instance Is Removed From The Pool.
A Connection Pool is maintained internally by the Connection Pool Manager. When a request for a subsequent connection comes in, the Connection Pool Manager searches the pool for the availability of a free connection and returns it to the application if one is available. The following points elaborate how the Connection Pool Manager works- the operations that it performs when a request for a new connection comes to it.
· If any unused connection is available, it returns the connection.
· If all connections are used up, a new connection is created and added to the pool.
· If the number of connections reaches the maximum number of connections in the pool, the requests are queued until a connection becomes free for reuse.
Parameters controlling Connection Pooling
| Max Pool Size | The maximum number of connections allowed in the pool. The default value is 100 |
| Min Pool Size | The minimum number of connections allowed in the pool. The default value is zero |
| Enlist | Signals whether the pooler automatically enlists the connection in the creation thread's current transaction context. The default value is true |
e.g.
DataSource=TestServer;Initial Catalog=Northwind; User ID=Chester;Password=Tester;Max Pool Size=50;Min Pool Size=5;Pooling=True;
You should refer to the documentation if you're using a .NET Data Provider other than SQL Server. Other data providers may have more pooling options. A good example is the Oracle Data Provider, which offers two options -- Decr Pool Size and Incr Pool Size -- for controlling how a connection pool may shrink or grow.
Improving Connection Pooling Performance
· We should always open the connections late and release them early; in other words, immediately after we are done using it.
· Connections should be opened only at the time when they are actually required. Otherwise it would decrease the number of available connections in the Connection Pool and, hence, have detrimental effects to the operation of the Connection Pool and the application's performance.
· The connections should be explicitly released immediately when we are done using it. This would facilitate better Connection Pooling as the connection would be returned to the pool and be available for reuse
Below are two code snippets:
SqlConnection sqlConnection = new SqlConnection(connectionString); try { sqlConnection.Open(); //Some Code } finally { sqlConnection.Close(); }
using(SqlConnection sqlConnection = new SqlConnection(connectionString)) { sqlConnection.Open(); //Some Code }
Few more points for better utilization of the Connection Pool.
· Always open connections when needed and close it immediately when you are done using it.
· Close the user-defined transactions before closing the related connections.
· Ensure that there is at least one connection open in the pool to maintain the Connection Pool.
· Avoid using connection pooling if integrated security is being used.
Conclusion
A Connection Pool, a container of connection objects, remains active as long as there are active or open connections in it. When a request for new connection comes in with a connection string, a new Connection Pool is allocated. We can improve the performance and scalability of our applications by using the same connection string in our applications.
However, we should use Connection Pooling appropriately as using it inappropriately might have negative effects to the overall performance of our applications. MSDN says, "Connection pooling is a powerful functionality that can improve your applications' performance. But if you aren't a good lifeguard, your connection pools can become a detriment instead of a benefit. For e.g.
“If an application is in constant communication with a database, then connection pooling may be optimal since the need to open/establish connections is negated, thus performance improves. On the other hand, an application that runs nightly does not need to maintain a pool since it does nothing with the database the rest of the day. Use your best judgment when deciding whether to use connection pooling.”
Hope this helps.
Regards,
Arun Manglick
Disclaimer: The information contained in this message may be privileged, confidential, and protected from disclosure. If you are not the intended recipient, or an employee, or agent responsible for delivering this message to the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by replying to the message and deleting it from your computer.
No comments:
Post a Comment