title: SqlServer better to batch statements or foreach?
body: <p>Hypothetically, is it better to send N statements to Sql Server (2008), or is it better to send 1 command comprising N statements to Sql Server? In either case, I am running the same statement over a list of objects, and in both cases I would be using named parameters.  Suppose my use case is dumping a cache of log items every few hours. </p>
  
  <p>foreach example</p>
  
  <pre><code>var sql = "update blah blah blah where id = @id";
  using(var conn = GetConnection())
  {
      foreach(var obj in myList)
      {
           var cmd = new SqlCommand() 
           {CommandText = sql, Connection = conn};
           //add params from obj
           cmd.ExecuteNonQuery();
      }
  }
  </code></pre>
  
  <p>batch example</p>
  
  <pre><code>var sql = @"
     update blah blah blah where id = @id1
     update blah blah blah where id = @id2
     update blah blah blah where id = @id3
     -etc";
   using (var conn = GetConnection())
   {
       var cmd = new SqlCommand
       { CommandText = sql, Connection = conn};
  
       for(int i=0; i&lt;myList.Count; i++)
       {
           //add params: "id" + i  from myList[i]
       }
       cmd.ExecuteNonQuery();
   }
  </code></pre>
  
  <p>In time tests, the batch version took 15% longer than the foreach version for large inputs. I figure the batch version takes longer to execute because the server has to parse a huge statement and bind up to 2000 parameters.  Supposing Sql Server is on the LAN, is there any advantage to using the batch method?</p>