Which is a better approach to building a complex object (eager loading). A single call to a stored procedure that returns multiple result sets or multiple calls to stored procedures with one result set each? I'm building my DAL using T4/text templates in .NET so I'm leaning towards to latter.
Thanks!
This depends on the overall architectural design model of your application. If you are using what is called Table Data Gateway, or Active Record, then the latter is probably better. If you are using what is called Domain Model or Domain driven Design, then the former is much more appropriate.
I can't remember the last time we wrote a stored procedure that returned more than one resultset in one go. It's rare that it's actually an overall benefit.
The only reason I can think of to ever do something like that is for (a) performance-- e.g. if the SELECTS require temporary tables or a lot of setup that would be common to all of them, or (b) if data are at risk of changing in between calls and want to use serializable isolation semantics.
If all of the rowsets are mutually independent and have no set up in common, definitely separate them out. The cost of the round trips is not really a significant factor assuming you're using the lower level connectivity protocols (e.g. TCP/IP or named pipes, and not some kind of chatty technology like SQL over XML). What's more, if you can call the sprocs separately, that means you can call them asynchronously and in parallel and maybe even achieve superior performance if you wish to take the trouble.
