When I have to extract data from multiple tables, which approach is more efficient: creating one stored procedure that uses ADO's NextResultSet method to return all the result sets or creating a stored procedure for each result set?
The efficiency of these solutions depends on several factors. Creating one stored procedure that returns all the result sets is effective as long as you use NextRecordSet (the ADO Recordset method call to fetch the next set of results) to process the result sets quickly. Failure to process the results quickly can lead to extended locking on the server.
Creating a stored procedure for each result set works well because it permits code reuse. You can let other programs access the data by using each stored procedure as a standalone API without having to worry about coding the other programs to process result sets they might not want. You can still obtain multiple recordsets by combining the calls to each smaller stored procedure in a wrapper stored procedure that contains no logic other than passing parameters to each procedure and error handling. This solution also lets you use smaller stored procedures that compile faster if the need for recompilation arises. Because of the high cost of compilation, we recommend this modular approach.
Say you're building an application that's going to be used on a network with high network latency between the client and the server--for example, on a network with user sites connected over low bandwidth or multiple router hops. In that case, using the ADO Command object with bound parameters to call each stored procedure individually can be more efficient because binding parameters avoids the metadata chitchat that ADO engages in while it determines which data types are being returned from the server. We tend to use this technique when we're conducting performance benchmarks.