Home  »  Articles  »  ADO.Net  »

What is Connection Pooling in .Net?

 Posted By : Manoranjan SahooPosted Date : 01/12/2010 02:57:48 PM Category : ADO.Net Points : 10 (Rs 1.00)

Introduction :

Sometimes we need to work arround huge data with huge number of users. If we create a website and that is used by many members at a time. Then, the fetching of data from database becomes slow, because there are many number of connections getting opened. To avoid this problem, .net provides one best feature named "connection pooling". So we have to use this in our website or Windows application in the connection string.

ConnectionString :

I think you all know about connection string. It is something like an database server address for connecting an application to the database wherever the Database is located.

For Example :


SqlConnection sqlcon=new SqlConnection("Data source=DBServer;database=DBName;user id=userid;password=loginpassword;connect timeout=0;")


What is Connection Pooling?

A Connection Pool is a container of open and 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. By default a connection string is enabled with connection pooling. The default value the maximum number of pools is 100 and minimum is 0.

How Connection Pooling works?

We said that we have 100 pools as default. When the first user enters into our website, it tries to open one connection pool, so the first connection pool is created. Now 99 pools are remaining. At the same time, one more user enters into open a connection with the same string then our pooler checks whether any opened pools with same connection string are free or available. If the first user closes the connection, that pool will be free and this user starts using the same pool. So the second user also uses the same pool and we can get the data quickly. Here it gives high performance. Suppose if the pool is not freed and the first user is still using it, what the pooler will do, it creates one new pool from the remaining 99 pools and use that. Like this, when n number of users try to establish a connection if the pools are free it will reuse the same pool or if 100 pools are engaged, it will wait for a pool to be free. If the pool is getting free in a particular time, it will use it otherwise the waiting connection will expire. So for huge number of user in websites, 100 pools will not be enough. So we have to allow more pools.

Note : If the connection string is different, it will create a new pool.

Ex :

Below we can see the connection string created, here I am using a separate class for connection establishment and connection opening. If our website is huge, we don't want to write the connection string everywhere. Just create a connection in a class.

Here the class name is:
    public class MyConnection

We are establishing the connection string in the constructor of the class. So when we create the object for the class, the connection string is ready. We are using a string builder for creating the connection string, so if there is any change we can alter it later very easily.

SqlConnectionStringBuilder strsqlbuild = new SqlConnectionStringBuilder();

In this code below, you can see the pooling details I have given:


public class MyConnection
{
        //For switching between MSAccess and MSSQL two channel is instantiated
        SqlConnection sqlcon = new SqlConnection(); //making the instance of sqlconnection
        OleDbConnection oledbcon = new OleDbConnection();//making the instance of oledbconnection
        SqlConnectionStringBuilder strsqlbuild = new SqlConnectionStringBuilder();//making the instance for the sqlConnection String builder
        OleDbConnectionStringBuilder  stroledbbuild =  new OleDbConnectionStringBuilder();//making the instance for Oledbconnection string builder
      
        public  MyConnection()// create constructor
        {
            SqlConnectionStringBuilder strsqlbuild = new SqlConnectionStringBuilder();//making the instance for the sqlConnection String builder
             strsqlbuild.InitialCatalog = "mydatabase";
             strsqlbuild.DataSource = "MYDBSERVER";
             strsqlbuild.UserID = "sa";
             strsqlbuild.Password = "mydatabaseloginpassword";
             strsqlbuild .Add ("Max pool size",1500);
             strsqlbuild .Add ("Min pool size",20);
             strsqlbuild.Add("Pooling", true);
             sqlcon.ConnectionString = strsqlbuild.ConnectionString;     
        }
    
        public void opensqlcon(SqlConnection connect)//function for opening the SQL connection which will close the function if it is opened already
        {
            if (connect.State == ConnectionState.Open)
            {
                connect.Close();
            }           
            connect.Open();
        }
        public string sConName//Property for retrieving the sql connection string
        {
            get
            {
                return sqlcon.ConnectionString;
            }
            set
            {
                sConName = value;
            }
        }      
}


Now I have one method for opening the connection and property for getting the connection string.

if you want to open the connection, then create the object of this class and access the opensqlcon(connection object).
The property is used for accessing the connection string.

Responses
 Author : Jitendra Zaa Member Level : BronzeDate : 09/12/2010 02:11:41 PM Points : 2

Good article manoranjan.

I have few more points, which can be usefull for this article:

The following points illustrate the measures that can be taken 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.

Connection Pooling can be monitored in one of the following ways:

  • Executing the sp_who or sp_who2 stored procedures
  • Using the SQL Server Profiler
  • Using the Performance Monitor and Performance Counters

Thanks,

Jitendra Zaa



 Author : Manoranjan Sahoo Member Level : BronzeDate : 09/12/2010 02:16:40 PM Points : 2

yes jitendra. you are right. all the points what your mentioned above are very usefull.

Thanks for sharing these valuable points.



 Author : Purushothaman Panamayan Member Level : BronzeDate : 21/12/2010 05:06:05 PM Points : 2

Hi,

 

  Nice Explanation.Keep it up.



Post Reply
Login to post Response.