ADO.NET SQL Server performance: multiple result sets vs. multiple command executions

With connection pooling or at least the assumption that the connection is not closed between calls, is there a network or server performance difference and how significant is it between one stored procedure execu开发者_如何学JAVAtion with multiple result sets and multiple stored procedure executions.

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));
      }
    }
  }
}

vs

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));
    }
  }
}

The first one is a single round-trip to the server, the second is distinct round trips. A round trip occurs a penalty due to network latency, time to parse the request, time to set up an execution context etc. However, this penalty is all but negligible for everything but the most critical applications.

So do whatever is easier to understand, code, debug and maintain (imho, that would be the second option). You probably won't be able to measure the difference.

I disagree, I would go with the 1st approach almost always (it really depends on the particular scenario) but in general, it's better to have a proc returning 2 result sets as opposed to having 2 calls to 2 different procs that return a single data set precisely for the reasons @Remus explained (network latency,etc).

In the majority of the cases the difference is not negligible.

Suggest you profile the difference for yourself. What is most efficient may depend greatly on how much data and how many users etc. I would tend to believe that the one round trip over the network is better, but it is best to try both approaches and measure and then you will know.

You can assume that Connection Pooling is being used in both of your scenarios, so it's really a non-factor in your determination of efficiency.

If you can receive all of the results in a single call, it is naturally more efficient than multiple calls. Consider the simple case of selecting 10 things individually versus selecting all 10 at once with an 'in' clause. That's 1 query sent to the server and 1 response parsed vs 10 of each. This is the round-trip that Remus is talking about.

It's most likely nominal in light usage scenarios, but as (if) you scale up, the chattiness could start to be a problem. Your connection pool has a limit that can be reached at some point.

I would go with option 1 if you are returning the same type of data between calls.

However, there is also maintenance and reuse to consider. If you are returning disparate data (ie: fetching all data needed for a particular view), I would go with option 2 and optimize to fewer calls as needed.