This week's book giveaway is in the General Computing forum. We're giving away four copies of Arduino in Action and have Martin Evans, Joshua Noble, and Jordan Hochenbaum on-line! See this thread for details.
I am in the process of writing a stored procedure that is expected to return thousands of record. Can you please suggest whether the results returned as a list is better in performance or an XML being returned is better? Will we run into any performance issue because db is trying to generate a big xml response?
The result currently looks like following in a list
XML of following structure is the one I have in mind.
welcome to the Ranch Roshan. this is just my best estimating but the biggest bottleneck is often sending the data down the wire.
So the less data you send the better, the xml is a lot more data than the raw results.
Wendy is right: you should try to keep the volume of data being passed around as small as possible, so XML is probably not a good idea here.
It might also be worth asking why you are returning thousands of records from the database anyway.
If you are just looking for summary data, then obviously you should simply fetch the summary data from the database and let the database do all the hard work of creating the the summary - that's what it's designed for.
If you are doing extra processing on the returned data, is this the kind of thing that could be done in a stored procedure instead, to reduce the volume of data you have to send back from the database?
This will depend on your requirements and your local architectural/design choices, but having made the decision to use stored procedures, it is worth trying to maximise the benefits you gain from them.