FOR XML and Performance

Question

Does anybody have any tips/tricks as to how to improve performance in the following scenario:

• I have a record set of approx. 500,000 rows.
• There's a lot of data being pulled for this query. In order to minimize the query time, I've broken this up into several smaller queries; all stored in temp tables with indexes and such. At the end; I assemble all the smaller queries into one temp table to serve as the "master" record for the final query.
• Current execution time for the above step is approximately 2 minutes (not too bad, considering the amount of data being pulled)
• The final result needs to be delivered in XML format. When writing my final query, I'm using FOR XML EXPLICIT to put this into the proper format.
• The problem I'm running into is that the above XML query is taking 30+ minutes, which is way too long in this case.

So, I do have a couple options here:

• I'd be able to "pre-load" this data into an actual table, and then query off that instead of using temp tables. I'm not sure this will buy me much time, as the issue isn't with loading the data, but rather the long time that it takes to generate the XML.
• The above XML will eventually be loaded into our C# codebase to be uploaded to its final destination. Does it make more sense to simply load the record set into C#, and do the XML transformation there, instead of doing it on the database side?
• Right now I'm using FOR XML EXPLICIT; are there any performance benefits gained by using any of the other XML modes (RAW, AUTO, PATH)?

Thanks again.

~Jim

Answer 1

This is your option right here. XML transformation is going to be far more efficient in C# than in SQL.

The above XML will eventually be loaded into our C# codebase to be uploaded to its final destination. Does it make more sense to simply load the record set into C#, and do the XML transformation there, instead of doing it on the database side?