Fixing my connection pooling benchmarks
The numbers in my last post were a little misleading (as you might expect from un-scientific homebrew benchmarks). I showed that ExplicitOpen (2.5 seconds) was slightly faster than ImplicitOpen (2.9 seconds), but both were significantly faster than NoConnectionPooling (7.5 seconds). The whole point of the test was to see how the performance of running 2 queries within a method could vary, based on different approaches. However, in order to get a larger sample size, I ran the methods within a loop for 1000 iterations. It was intended to just give me more test runs, but the loop actually had a side effect. When connection pooling was enabled (for ExplicitOpen and ImplicitOpen), the same connection was used for all 1000 iterations. I had only wanted the same connection to be used for the 2 queries within the methods. So when I turned connection pooling off, the dramatic difference wasn't because running the 2 queries was much slower - it was because I was now making 2000 connections to the database instead of 1.
If I wanted to compare the performance of running the 2 queries with connection pooling on and off, within a loop, I would need to make sure each iteration of the loop used a new connection in all 3 scenarios (ExplicitOpen, ImplicitOpen, NoConnectionPooling).
An easy way to force a new connection is to change the connection string. I changed the signature of each of the methods to accept an integer argument:
static void ExplicitOpen(int iteration)
I then added an Application Name property to the connection string; altering the application name for each iteration:
"data source=(local);initial catalog=issuevision;integrated security=true;application name=TestCP" + iteration.ToString()
The new results:
ExplicitOpen 9.0
ImplicitOpen 9.5
NoConnectionPooling 7.6
These results show that ExplicitOpen is still faster than ImplicitOpen, but now NoConnectionPooling is much faster than the other 2. How can this be? Well, since none of the scenarios are reusing a connection across iterations, you gain an advantage by removing the connection pooling overhead by explicitly disabling it in the connection string.
The original conclusion was correct (ExplicitOpen is slightly faster than ImplicitOpen), but it is difficult to measure the relative performance with connection pooling turned off. The results of running a single iteration of each method vary too widely to draw any conclusions -- you need the multiple iterations for things to average out. But once you introduce multiple iterations, you blur the effect of connection pooling.
As a bonus, we can now conclude that if you know you are not going to re-use a connection (rare, unless each user of your application is given their own database login), it is better to turn off connection pooling explicitly.