I am working on Stored Procedure. Which uses view and converts the resultset table to XML format. We have used the statement 'FOR XML AUTO, ROOT('toxicsite'), TYPE'. The View is returning me the 14k records and after its getting converted to XML ... It takes 2 min. Need help for another alternative or How I can Optimize the query Transform
I just tried this with a simple SELECT TOP 14000 * FROM SomeBigTable. SSMS is ready after 2 secs. With FOR XML AUTO it returns quite as fast. I do not think, that creating the XML is taking that long...
If your VIEW is very complex it might be, that AUTO is out tricking you. AUTO tries to find an appropriate internal structure for your query (nestings of related data)
As a quick shot you might try FOR XML RAW,TYPE just to check the performance difference. Best was an explicit approach with FOR XML PATH, where you can specify the nestings and relations yourself.
If there are BLOBs involved (VARBINARY data), you have the extra costs of converting this to base64, if there are many strings involved, especially if they have a lot of non-latin characters, you have the extra costs of entity encoding...
In general XML is astonishingly fast...