Follow-up for Connection Pooling...

I had mentioned in one of my recent posts that I was told we are not using connection pooling in our C# web service code. I had done some reading, and it seemed pretty clear that connection pooling is enabled by default in the .Net framework, and that you would need to explicitly disable pooling.

I looked through our code and I couldn't find any instance where we set pooling to false for our connection strings. I also wrote some simple code to test if the classes we are using (from Microsoft.Practices.EnterpriseLibrary.Data) might do something odd that would prevent connection pooling, or require us to explicitly state that we want to use connection pooling.

The result of my test code was that the code where I explicitly set "pooling=false" took a longer time to execute than the code without "pooling=false". That shouldn't be surprising, but I was told by multiple people that the code was not using connection pooling. It is understandable why people thought the code was not using pooling, though. For one, there sometimes is a feeling that as code gets old, then the quality of the design degrades. Also, there are lots of coding styles and myths that get passed around as fact. This has been a concrete example for me that everyone should try things out before just accepting something as a fact when it comes to writing software. In addition, it pays to read the documentation!

The test code looks like this:

private const string pooled =
    @"Data Source=someserver;Initial Catalog=dbname;Integrated Security=SSPI;";
private const string notpooled =
    @"Data Source=someserver;Initial Catalog=dbname;Integrated Security=SSPI;Pooling=false;";

public static void Main(string[] args)
{
    int testCount = 10000;
    
    GenericDatabase db = new GenericDatabase(pooled, SqlClientFactory.Instance);
    Console.WriteLine("    pooled : {0}", RunConnectionTest(db, testCount));

    db = new GenericDatabase(notpooled, SqlClientFactory.Instance);
    Console.WriteLine("not pooled : {0}", RunConnectionTest(db, testCount));
}

private static TimeSpan RunConnectionTest(GenericDatabase db, int testCount)
{
    Stopwatch sw = new Stopwatch();
    sw.Start();
    DbConnection conn;
    for (int i = 0; i < testCount; i++)
    {
        conn = db.CreateConnection();
        conn.Open();
        var schema = conn.GetSchema(); // just to make the code do something
        conn.Close();
    }
    sw.Stop();
    return sw.Elapsed;
}

I specifically did not use using statements for the connection. I wanted to mimic what the service code is doing.

The result of the test was that the loop using the connection string that implicitly uses connection pooling finished in just over 1 second. The loop for the connection string that explicitly says to not use connection pooling took about 30 seconds.

I'm pretty happy to know that we don't need to do any updates to our code to make it use connection pooling!