I've been writing some new Stored Procedures, and find myself returning between 6-8 result-sets in a few cases. I've always worked under the assumption that there were certain inefficiencies with result-sets that should be avoided.
It would appear that my options are to use result-sets or construct an xml representation of the complex objects and return them using SQL-XML channels (this is to avoid truncation of the blogs, by ADO clients). Does anyone have any experience with the performance and/or relative efficiencies of these choices and maybe provide some insights of the bad and good of each?
For additional context…
- Each result-set would have no more than 30 columns, with most around 10
- Column data is mostly integers (1 to 8 bytes) and some strings between 50 and 2000 characters
- During peak load we expect between 5-9 queries per second for the SPROCs in question although there is one Use Case that could be 10’s per second
- Denormalizing to a flatter view is not really a reasonable option insofar as we can see
Best Answer
I prefer multiple resultsets because its faster and uses less memory and much easier to deal with.
But everything depends on consumer of the data - for example if it can receive only XML - you have no choice 8-)