For any web application establishing connection of database is most time consuming & slow ( which is always depends upon network speed & memory)
Generally we are using at the most 1-3 connections for an application and we are opening the same connections repeatedly and closing the same ,we can avoid the memory overhead and time for connection establishment by using .Net Supported Connection Pooling. Connection Pooling concept is used irrespective of the number of the user.
For all data providers in ADO.NET by default Connection Pooling is enabled, we can explicitly disable is by adding Pooling = false to Connection String.
Let us understand the basic concept of connection pooling – When application is accessed first time then at that time in memory Container is getting created for maintaining the Opened & used connections.When new user give a call to OPEN connection , it(Connection pool manager) will check if the connection is
pooled (i.e. already opened ) and then if available then it returns the same instead of creating the new.On the other hand when certain call give call to CLOSE connection then instead of closing the connection it will maintain the connection in the Pool ,so that it can able to provide the same to next OPEN call for the same connection.A Connection Pool is released from the memory when the last connection to the database is closed.
SqlConnection sqlConnection = new SqlConnection();
sqlConnection.ConnectionString =
“Data Source=dataSource; Initial Catalog=mydatabase; User ID=userID; Password=password;Connection Timeout=60; Min Pool Size=5; Max Pool Size=40;”
// A Connection Pool is created when call to Open() is given.
sqlConnection.Open();
As per MSDN,Each connection pool is associated with a distinct connection string. When a new connection is opened, if the connection string is not an exact match to an existing pool, a new pool is created. Connections are pooled per process, per application domain, per connection string and when using integrated security, per Windows identity.Only connections with the same configuration can be pooled. ADO.NET keeps several pools concurrently, one for each configuration. Connections are separated into pools by connection string, as well as by Windows identity when integrated security is used.
We can control the Connection pooling with SQL Server by mainly using following parameters of the ConnectionString ( Create Table) Connect Timeout/Connection Timeout – Default = 15 seconds ,controls the wait period in seconds when a new connection is requested, if this timeout expires, an exception will be thrown.
|
Parameter Name |
Default |
Used For |
|
Max Pool Size |
100 |
Specifies the maximum size of your connection pool. Most Web sites do not use more than 40 connections , but it depends on how long your database operations take to complete. |
|
Min Pool Size |
0 |
Initial number of connections that will be added to the pool upon its creation. however, you may chose to set this to a small number such as 5 if your application needs consistent response times even after it was idle for hours. In this case the first user requests won’t have to wait for those database connections to establish.
|
|
Pooling |
true |
Controls if your connection pooling on or off. When true, the Connection object is taken from pool, or if necessary, is created and added to the appropriate pool. known values are true, false, yes, and no. |
|
Connection Lifetime |
0 |
Value zero means maximum connection timeout. |
|
Connection Reset |
true |
Determines whether the database connection is reset when being drawn from the pool |
|
Enlist |
true |
When true, the pool manager automatically enlists the connection in the creation thread’s current transaction context. |
Check MSDN for Connection String Parameters for Pooling.
Let is consider One Example
SqlConnection sqlConnection = new SqlConnection();
sqlConnection.ConnectionString =
“Data Source=dataSource; Initial Catalog=mydatabase; User ID=userID; Password=password;Connection Timeout=60; Min Pool Size=5; Max Pool Size=40;”
// First Connection Pool is created when call to Open() is given.
sqlConnection.Open();
SqlConnection sqlConnection1 = new SqlConnection();
sqlConnection1.ConnectionString =
“Data Source=dataSource1; Initial Catalog=mydatabase1; User ID=userID; Password=password;Connection Timeout=60; Min Pool Size=5; Max Pool Size=40;”
// Second Connection Pool is created when call to Open() is given because ConnectionString is different from first ConnectionString
sqlConnection1.Open();
SqlConnection sqlConnection2 = new SqlConnection();
sqlConnection2.ConnectionString =
“Data Source=dataSource; Initial Catalog=mydatabase; User ID=userID; Password=password;Connection Timeout=60; Min Pool Size=5; Max Pool Size=40;”
sqlConnection2.Open();
// sqlConnection2 is assigned with existing Connection Pool A as the same connection string is used.
Take a note of following points,Usefull to get Connection Pool performance effectively
1) Connection Pooling concept mainly depends on the concept of reusable Connection Strings,Connections with same connectionstrings are to kept in a connection pool.So for this reason if we are going use the same connection throughout the code , create it in the Web.config and access it from there ( ref previous blog entry)By using this we can use the same connectionstring without bothering it will be the exact same as previous used, bcoz any change in the connection string causes new connection pool creation.
2) Open the connection only when it is required just before using the connection, because when we open the connection then connection is taken from the previous created pool or new pool is getting created and after opening the connection if we are not using the connection then resources are getting waisted also connections will close only after actual use and leads to the loss of a connection in pool , that might cause creation of new pool or thowing Exception “MAX Pool Size Reached” at time of heavy traffic.
3)Close the connection explicitly by calling Close() after the activity is done or after the tranasction is completed.Closing connection is important beacause when we give call to Close() connection ,connection is being passed/added to connection pool so that other one can use it. Missing the connection close sometimes leads to connection leaks, as number of open connection increased and max pool size obtained. If you think you are lazy enough to close the connection then open all the connections inside the “using” loop .
using (SqlConnection connection = new SqlConnection(“Data Source=dataSource; Initial Catalog=mydatabase; User ID=userID; Password=password;Connection
Timeout=60; Min Pool Size=5; Max Pool Size=40;”))
{
connection.Open();
}
4)Avoid connection pooling when we are planning to use Basic or Windows Authentication,Connections are pooled according to the connection string plus the user identity.so in the above case we will get one pool per user,which is not available to other users leads to poor performance by leaving large number of connections open while consuming memory.
As per MSDN –
Pool fragmentation is a common problem in many Web applications where the application can create a large number of pools that are not freed until the process exits. This leaves a large number of connections open while consuming memory, resulting in poor performance.
Pool Fragmentation Due to Many Databases
Many Internet service providers host several Web sites on a single server. They may use a single database to confirm a Forms authentication login and then open a connection to a specific database for that user or group of users. The connection to the authentication database gets pooled and used by everyone.However, there is a separate pool of connections to each database, thus increasing the number of connections to the server.
This is also a side effect of the application design. There is a relatively simple way to avoid this side effect, however, without compromising security when connecting to SQL Server. Instead of connecting to a separate database for each user or group, connect to the same database on the server and then execute the Transact-SQL USE statement to change to the desired database. The following code fragment demonstrates creating an initial connection to the master
database and then switching to the desired database specified in the databaseName string variable.
// Assumes that command is a SqlCommand object.
using (SqlConnection connection = new SqlConnection(
“Server=MSSQL1;uid=xxx;pwd=xxx;database=master”))
{
connection.Open();
command.ExecuteNonQuery(“USE ” + databaseName);
}
Detail Ref – MSDN
CollectedDotNet 