Database connection pooling in ADO.Net

Imagine a situation that multiple users are connecting the same database through our application. In an application establishing a database connection and logging into it is a time consuming activity. It needs several seconds for every time it establishes a connection. So opening and maintaining a database connection for each user is not an ideal solution. When the number of users increases, and if the system�s memory set is limit, probably it leads to a system crash.

This problem can solve through connection pooling. A connection pool is a cache or pool of database connections maintained by the application so that the connections can be reused when the database receives future requests for data.

Establishing once and reserving it for future request can essentially improve the overall performance of the application. In connection pooling, after a connection is created, the connection object is placed in the pool. When a simultaneous connection request is made later, the application does not establish a new connection. It uses the same connection object in the pool over again. If all the connections are being used, a new connection is made and is added to the pool. It eliminates some amount of time that the user waits for initializing a database connection every time. A Connection Pool is released from the memory when the last connection to the database is closed. Multiple connection pools can also be created in the same application domain. All the objects in a connection pool must be unique. Note that if another request comes in with a different connection string for the database, another Connection Pool would be created.


For e.g.:

Listing 1:


//Here a pool is created.
sqlcon = new sqlConnection(�server=SQLDB; uid=sa; pwd=admin; database=Dev�);



Listing 2:


//This time another pool is created because connectionstring have some //changes(userid and password is different in Listing 1.)
conn = new sqlConnection(�server=SQLDB;userid=Dev; password=Dev; database=Dev�);


Listing 3:


//No new pool is created. Here using the same pool of Listing 1 because here //connectionstring is the same of that.
sqlcon = new sqlConnection(�server=SQLDB; uid=sa; pwd=admin; database=Dev�);



In JDBC (Java database connectivity API) DBConnectionPool Class is used to manage multiple connection pools. In the case of PHP, mysql_pconnect function (if the backend of the application is MySQL) will take care of it by checking an identical connection was there in the webserver, if it exists it uses it. If the backend is SQL Server, we can use mssql_pconnect() instead of it. Ora_plogon() is used for establishing a persistent connection between php and Oracle database. But in the case of ADO.Net I don�t know, how much among us heard about it because connection pooling is working by default in it. The Data Providers in ADO.NET have Connection Pooling turned on by default; if you need to turn it off, specify Pooling = false in the connection string being used. If you are using Oledb coonection specify OLE DB Services = -4 instead of it in the connection string.

Connection pools have a maximum limit of connection objects (We can changed its default value). Connections are automatically removed from the pool when they remain inactive for a long period of time.


IMPORTANT:

For the efficient working of pooling, don�t forget to close the connections once you are done with it so that it returns to the pool.


These are some of the parameters passed to a connection string for connection pooling:
� Connect Timeout - specifies the wait period in seconds when a new connection is requested, if this timeout expires, an exception will be thrown. Default is 15 seconds.
� Max Pool Size - specifies the maximum size of your connection pool. Default is 100.
� Min Pool Size - The minimum connections that are always open in the pool. Default is Zero.
� Pooling - Setting the connection pooling on or off. Default Value is true.

You can monitor the performance of application due to connection pooling by executing sp_who or sp_who2 stored procedures in SQL Server or in Performance Monitor or performance Counters in Control Panel -> Administrative Tools of Windows Operating System. You can also use SQL Server Profiler for this.

For Oracle to know the pooling performance: Execute the following query -- SELECT * FROM V$SESSION WHERE PROGRAM IS NOT NULL

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. In .net, in this case application will return an Exception called System.InvalidOperationException.
This exception will generate also in the case of if maximum timeout of the connection expired. So we may want to catch it.

Code:

conn = new SqlConnection(); try {   conn.ConnectionString = "server=SQLDB;userid=Dev; password=Dev; data source=Dev;Min Pool Size=5;Max Pool Size=60;Connect Timeout=25;";     // Here Connection Timeout is set for 25 seconds!   conn.Open(); } catch(Exception) {   if (conn.State != ConnectionState.Closed) conn.Close();   conn.ConnectionString = "server=SQLDB;userid=Dev; password=Dev; data source=Dev;Pooling=false;Connect Timeout=45;";   conn.Open();

Note: SqlClient connection pooling that uses integrated security is slower than OleDb(Refer: http://support.microsoft.com/kb/316757)
(This is a BUG happened in .NET Framework Class Library namespace System.Data)

So when we use integrated Security in SqlClient connection it is better to avoid connection pooling by setting �pooling = false� in the connection string.

ADO.NET 2.0 introduces two new methods to clear the connection pool: ClearAllPools and ClearPool. ClearAllPools clears all the active pools of a given provider and ClearPool clears a specific pool that is associated with a specific connection.


E.g.:

Code:

// Clears all the pools System.Data.SqlClient.SqlConnection.ClearAllPools(); // Clears a specific pool sqlcon = new sqlConnection(�server=SQLDB; uid=sa; pwd=admin; database=Dev�); SqlConnection.ClearPool(sqlcon);

Hope you all enjoyed this article.