Connection-Pooling vs. Reusing one connection

I recently had a conversation about .NET connection pooling, and how this compares to reusing a single open connection.  I have always “known” that connection pooling helps, but that it isn’t as efficient as reusing the connection.  Since I couldn’t provide proof of my belief, I decided to write a test.  I decided to execute a simple T-SQL statement 1,000 times and record how many milliseconds elapsed.  I would then vary the way that connections are managed and see how this affected the elapsed time.

First, I wanted to test using a single connection:

      Dim sw As Stopwatch = Stopwatch.StartNew

      Using conn As New SqlConnection(My.Settings.ConnectionString)
          conn.Open()

           For i = 1 To 1000
               Using cmd As New SqlCommand("select * from dbo.person", conn)
                    Using rdr As SqlDataReader = cmd.ExecuteReader()
                         While rdr.Read
                         End While
                    End Using
               End Using
          Next
     End Using

      sw.Stop()
     Dim elapsed As Long = sw.ElapsedMilliseconds

      MessageBox.Show(elapsed.ToString)

I called this test “OC” for “One Connection”.

Next, I wanted to test opening and closing the connection with and without connection pooling:

      Private Sub MultipleConnections(connectionString As String)

           Dim sw As Stopwatch = Stopwatch.StartNew

           For i = 1 To 1000
               Using conn As New SqlConnection(connectionString)
                    conn.Open()
                    Using cmd As New SqlCommand("select * from dbo.person", conn)
                         Using rdr As SqlDataReader = cmd.ExecuteReader()
                              While rdr.Read
                              End While
                         End Using
                    End Using
                    conn.Close()

               End Using
          Next

           sw.Stop()
          Dim elapsed As Long = sw.ElapsedMilliseconds

           MessageBox.Show(elapsed.ToString)

      End Sub

I called this two ways.  With pooling enabled (the default, called “CP”):

      MultipleConnections(My.Settings.ConnectionString)

and with pooling disabled (called “NCP”):

      MultipleConnections(My.Settings.ConnectionString & ";Pooling='false'")

I then ran each test 10 times and placed the results in a table (values indicate milliseconds):

Run #	NCP	CP	OC
1	4073	374	237
2	4032	341	298
3	3985	353	242
4	4085	348	269
5	3964	369	256
6	4203	330	207
7	4055	341	359
8	4071	357	286
9	3968	363	356
10	4023	349	359
AVG	4046	353	287

 

Now, what does this mean?  Clearly, if you disable connection pooling, then you will likely suffer a significant performance penalty.  I expected this.  I also expected that using one connection (OC) was faster than using connection pooling (CP).  In these tests, OC is roughly 19% faster than CP.  This sounds significant, but remember that the test code looped 1,000 times.  On average, a single OC call was .066 milliseconds faster than a single CP call.  Unless you are hitting the database many times, such as when running a multi-user ASP.NET application, you won’t notice six hundredths of a millisecond.  (The connection pooling system does a pretty good job of managing the connections.)  Still, it is good to see hard numbers back up what I “know”.