Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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 execution 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));
    }
  }
}
like image 629
Jimmy Hoffa Avatar asked Jul 09 '26 03:07

Jimmy Hoffa


1 Answers

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.

like image 53
Remus Rusanu Avatar answered Jul 10 '26 22:07

Remus Rusanu