Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question

1 Answer 1

up vote 1 down vote accepted

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

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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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