Improving FOR XML Explicit Perfomance

Question

Although using SQL FOR XML EXPLICIT is cumbersome, I find myself using it often. Since it allows full control over the generated XML document.

My problem is with perfomance, sometimes it would take more than 5 minutes to generate an XML document describing some financial data (invoices, bills .. etc.) for just one month!

So I was looking for tips to imrpove its performance. Or even other ways to replace it that give a performance advantage.

Answer 1

If the XML is being passed to some application for processing after being produced (such as a web application), one alternative is to just generate normal record sets in the SQL, and let the calling application create the XML document. Or perhaps the SQL can use FOR XML AUTO to generate a simpler form of XML than you require, and then the calling application can transform it into the correct XML format using an XSLT stylesheet.

Of course, you need to make sure there are no underlying performance issues with the original SQL whether it is formatting to XML or not (i.e. Is your database missing some indexes, perhaps?)