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

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-)

|improve this answer|||||
  • In this case, it's a .NET client with access to the full ADO.NET stack and no ORM's or anything like that. I'm the dev on both sides so the choice is really mine to make. With that said, I don't want to hand the future DBA a pile of poo so I'm trying to make wise choices now. :-) – JoeGeeky Jan 25 '12 at 21:58
  • I am .net guy too and my choice - multiple resultsets – Oleg Dok Jan 26 '12 at 0:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.