ADO.NET SQL Server Performance: Multiple Result Sets vs. Multiple Commands

Question

When pooling, or at least assuming that the connection is not closed between calls, is there a difference in network or server performance and how important is it between executing a single stored procedure with multiple result sets and executing multiple stored procedures.

In pseudo code, something like

using(new connection)
{
  using (datareader dr = connection.Execute(Command))
  {
    while (dr.NextResult())
    {
      while (dr.Read())
      {
        SomeContainer.Add(Something.Parse(dr));
      }
    }
  }
}

against

using(new connection)
{
  using (datareader dr = connection.Execute(Command))
  {
    while (dr.Read())
    {
      SomeContainer.Add(Something.Parse(dr));
    }
  }

  using (datareader dr = connection.Execute(Command))
  {
    while (dr.Read())
    {
      SomeContainer.Add(Something.Parse(dr));
    }
  }
}

Answer 1

- , - . , , .. , .

, , , (imho, ). , .

Answer 2

, 1- ( ), proc, 2 , , 2 , , @Remus ( ..).

.

Answer 3

. , , , .. , , , .

Answer 4

, Connection Pooling , .

, , , , . 10 10 "in". 1 1 vs 10 . , .

, , , , () , . , - .

1, .

However, there is maintenance and reuse. If you are returning disparate data (i.e.: retrieving all the data needed for a particular kind), I would go with option 2 and optimize for fewer calls as needed.