title: Is it better to execute many sql commands with one connection, or reconnect every time?
body: <p>Here's my test code, which seems to suggest that it's better to connect multiple times instead of connecting just once.</p>
  
  <p>Am I doing something wrong?</p>
  
  <pre><code>int numIts = 100;
  Stopwatch sw = new Stopwatch();
  sw.Start();
  using (SqlConnection connection = new SqlConnection(connectionParameters))
  {   
              connection.Open();
      for(int i = 0; i &lt; numIts; i++)
      {
          SqlCommand command = new SqlCommand(sqlCommandName, connection);
                  command.CommandType = CommandType.StoredProcedure;
                  command.Parameters.AddWithValue(par1Name, par1Val);
                  command.Parameters.AddWithValue(par2Name, par2Val);
          using(SqlDataReader reader = command.ExecuteReader())
          {
          }
      }
  }
  sw.Stop();
  TimeSpan durationOfOneConnectionManyCommands = sw.Elapsed;
  Console.WriteLine(durationOfOneConnectionManyCommands);
  
  sw.Reset();
  
  sw.Start();
  for(int i = 0; i &lt; numIts; i++)
  {
      using (SqlConnection connection = new SqlConnection(connectionParameters))
      {   
                  connection.Open();
          SqlCommand command = new SqlCommand(sqlCommandName, connection);
                  command.CommandType = CommandType.StoredProcedure;
                  command.Parameters.AddWithValue(par1Name, par1Val);
                  command.Parameters.AddWithValue(par2Name, par2Val);
          using(SqlDataReader reader = command.ExecuteReader())
          {
          }
      }                               
  }
  sw.Stop();
  TimeSpan durationOfManyConnections = sw.Elapsed;
  Console.WriteLine(durationOfManyConnections);
  </code></pre>
  
  <p>Output:</p>
  
  <pre><code>//output:
  //00:00:24.3898218   // only one connection established
  //00:00:23.4585797   // many connections established.
  //
  //output after varying parameters (expected much shorter):
  //00:00:03.8995448
  //00:00:03.4539567
  </code></pre>
  
  <p><strong>Update:</strong></p>
  
  <p>OK, so those who said it would be faster w/ one connection have it. (although the difference is marginal, if any.)
  Here's the revised code and output:</p>
  
  <pre><code>public void TimingTest()
  {
      numIts = 1000;
      commandTxt = "select " + colNames + " from " + tableName;
  
      OneConnection();
      ManyConnections();
      OneConnection();
  }
  private void ManyConnections()
  {
      Stopwatch sw = new Stopwatch();
      sw.Start();
      for (int i = 0; i &lt; numIts; i++)
      {
          using (SqlConnection connection = new SqlConnection(connectionParameters))
          {
              connection.Open();
              using (SqlCommand command = connection.CreateCommand())
              {
                  command.CommandText = commandTxt;
  
                  using (SqlDataReader reader = command.ExecuteReader())
                  {
                  }
              }
          }
      }
      sw.Stop();
      TimeSpan durationOfManyConnections = sw.Elapsed;
      Console.WriteLine("many connections: " + durationOfManyConnections);
  }
  private void OneConnection()
  {
      Stopwatch sw = new Stopwatch();
      sw.Start();
      using (SqlConnection connection = new SqlConnection(connectionParameters))
      {
          connection.Open();
          for (int i = 0; i &lt; numIts; i++)
          {
              using (SqlCommand command = connection.CreateCommand())
              {
                  command.CommandText = commandTxt;
                  using (SqlDataReader reader = command.ExecuteReader())
                  {
                  }
              }
          }
      }
      sw.Stop();
      TimeSpan durationOfOneConnectionManyCommands = sw.Elapsed;
      Console.WriteLine("one connection: " + durationOfOneConnectionManyCommands);
  }
  </code></pre>
  
  <p>Output:</p>
  
  <pre><code>one connection: 00:00:08.0410024
  many connections: 00:00:08.7278090
  one connection: 00:00:08.6368853
  
  one connection: 00:00:10.7965324
  many connections: 00:00:10.8674326
  one connection: 00:00:08.6346272
  </code></pre>
  
  <p><strong>Update:</strong></p>
  
  <p>the difference is more striking if I use <code>SQLConnection.ClearAllPools()</code> after each function:</p>
  
  <p>Output:</p>
  
  <pre><code>one connection: 00:00:09.8544728
  many connections: 00:00:11.4967753
  one connection: 00:00:09.7775865
  </code></pre>