May 16, 2012 at 11:49 pm
Hi,
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
custId prodId
------- -------
1 345
1 123
1 789
1 678
2 123
2 456
XML of following structure is the one I have in mind.
<group>
<customers>
<custId> 1 </custId>
<prods>
<prodId> 345 </prodId>
<prodId> 123 </prodId>
<prodId> 789 </prodId>
<prodId> 678 </prodId>
</prods>
</customers>
<customers>
<custId> 2 </custId>
<prods>
<prodId> 123 </prodId>
<prodId> 456 </prodId>
</prods>
</customers>
</group>
Please help me decide.
Thanks,
Roshan
May 17, 2012 at 12:01 am
I'm guessing from past experience that the rowset will return much faster than the XML because I've found that using SQL to construct the XML isn't particularly fast.
I would suggest you try it with a one million row table ala Jeff Moden and let us know for sure.
From his article (http://www.sqlservercentral.com/articles/Data+Generation/87901/) you can use the first snippet of SQL to generate that table.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply