Post
Ask Question

Understanding Connection Pooling

Introduction

 
This article is to go in deep into dome key features in the ADO.NET 2 which was shipped with VS 2005.
 
In this article, I will go through one of the key features which are Connection Pooling.
 
This feature is a key feature that plays an important role in the performance of most business applications or Data-driven applications.
 

What's Connection Pooling?

 
Connection pooling is the ability to re-use your connection to the Database. This means if you enable Connection pooling in the connection object, actually you enable the re-use of the connection to more than one user.
 
The connection pooling is enabled by default in the connection object. If you disable the connection pooling, this means the connection object which you create will not be re-used to any other user than who creates that object.
 
Shall I Enable/Disable Connection pool?
Let's do an example to use what the time has required if we enable/disable the connection pool in an application.
 
Sample 1(Connection Pooling is enabled):
 
 Create a console application and put the following lines of code to Main Method:
  1. SqlConnection testConnection = new SqlConnection(@ "Data Source=(local)\SQLEXPRESS;Initial Catalog=DEMO;Integrated Security=SSPI;");  
  2. long startTicks = DateTime.Now.Ticks;  
  3. for (int i = 1; i <= 100; i++) {  
  4.      testConnection.Open();  
  5.      testConnection.Close();  
  6. }  
  7. long endTicks = DateTime.Now.Ticks;  
  8. Console.WriteLine("Time taken : " + (endTicks - startTicks) + " ticks.");  
  9. testConnection.Dispose(); 
Run the application, on my machine the difference in time is:  937626 ticks
 
Sample 2(Connection Pooling is disabled):
 
Just add Pooling=false in the connection string.
 
Run the application, on my machine the difference in time is:  3906500 ticks
 
If you measure the difference you will see the time required by disabling the connection polling is 4 times greater than using connection pooling.
 
One of the good practices when using your connection object is enclosed your code by try {..} catch {} finally {} blocks.
 
On finally block you have to call Conn.Close(); or Conn.Dispose();
 
To remove all resources attached to that connection.
 
One of you asked what's the difference of calling Close or Dispose of, the answer doesn't use both of them, Dispose method actually call close method internally plus remove all allocated resource for that object to be garbage collected and at the same time, the underlying connection object can be pooled.
 

Conclusion

 
Use connection pooling in your applications to maximize the use of physical connection with the Database and your application.


Similar Articles