ADO.NET - simple test of connection pooling - Anuj Varma, Hands-On Technology Architect, Clean Air Activist

Introduction

By default, ado.net uses a connection pool – to reuse open database connections.

To see how dramatic the consequences of the connection pool are,

I wrote a simple test – showing the total time spent in opening and closing DB connections – both with pooling and without.

Skip to the ending - No pooling causes 4 times the overhead of pooled ado.net connections.

class Program
   {
       static void Main(string[] args)
       {
           SqlConnection pooledTestConnection = new SqlConnection(@"Data Source=localhost\SQLEXPRESS;Initial Catalog=TEST;Integrated Security=SSPI;");
           long startTicks = DateTime.Now.Ticks;
           for (int i = 1; i <= 100; i++)
           {
               pooledTestConnection.Open();
               pooledTestConnection.Close();
           }
           long endTicks = DateTime.Now.Ticks;
           Console.WriteLine("Time taken POOLED: " + (endTicks - startTicks) + " ticks.");
           pooledTestConnection.Dispose();
  
           SqlConnection unpooledTestConnection = new SqlConnection(@"Data Source=localhost\SQLEXPRESS;Initial Catalog=TEST;Integrated Security=SSPI;Pooling=false;");
           startTicks = DateTime.Now.Ticks;
           for (int i = 1; i <= 100; i++)
           {
               unpooledTestConnection.Open();
               unpooledTestConnection.Close();
           }
           endTicks = DateTime.Now.Ticks;
           Console.WriteLine("Time taken NO  POOLED: " + (endTicks - startTicks) + " ticks.");
           unpooledTestConnection.Dispose();
       }
   }

Summary

The result of the comparison is shown below – pooled connections clearly outperform non-pooled.

Anuj holds professional certifications in Google Cloud, AWS as well as certifications in Docker and App Performance Tools such as New Relic. He specializes in Cloud Security, Data Encryption and Container Technologies.

Initial Consultation

Anuj Varma – who has written 1217 posts on Anuj Varma, Hands-On Technology Architect, Clean Air Activist.