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):

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”.