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));
}
}
}
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With