What performs better: multiple single-result sql server stored procedures or single multple-result stored procedure

Question

Background: I use stored procedures exclusively for an ASP.NET application. I am using a DataReader to load a dataset object.

Answer 1

IMHO a single multiple-result stored procedure will perform better as only a single network roundtrip to the SQL server will be performed.

Answer 2

If it is a single dataset I would be using a single SP and have your SP return exactly what your dataset needs. It will be faster and more secure to have the SP do that work for you and one call for the connection would be more efficient than calling multiple times to load a single dataset.

Answer 3

Returning all of your data in a single query is almost always better, unless that puts too much onus on the app to manage the shape of that data. Of course, if your returning millions of rows, you'll probably want to break that up into something a little more lazy if possible :)

Answer 4

Single procedures returning single results (for code maintenability, simplicity and reuse of procedures) and then have one single call invoke all procedures needed:

command = new SqlCommand(@"
exec usp_proc1 @param1, @param2;
exec usp_proc2 @param1, @param3;
exec usp_proc3 @param2, @param4;");

Or you can create a wrapper procedure that simply invokes the individual procedures and call the wrapper. Overall, I say that the advantage of one round-trip is too good to be ignored.