FOR XML AUTO in stored procedure

Question

will FOR XML AUTO queries in stored procedures and using ExecuteXmlReader to retreive data to set business objects cause any performance hit?

Answer 1

I don't know about performance, but I have started doing things this way in order to take advantage of serialization, so I can pass BLL types as generics directly to the DAL for filling, and I like it a lot. It bypasses the Linq or typed DataSets, and uses a lot less code, whether machine-generated or not. As for performance, the best thing to do is run your own tests.

Update: If you're going to use FOR XML to serialize to BLL objects, don't use auto, use PATH, and specify the name of the root, otherwise it will use <row/> as the root element.

Answer 2

Hi,

It surely affects the performance if the amount of data you are going to retrive is relatively higher (The XML formatting is bigger than TDS rowset). I dont have the exact statistics with me. But tou can easily profile your queries with and without XML AUTO and find the facts. But XML AUTO surely takes much time than normal SQL quries.

I would say its preferrable to convert your recordsets to XML format in your application code than doing it in sql server.

EDIT:

T-SQL commands vs. XML AUTO in SQL Server - this article explains and gives the comparision between XML Auto and Normal TSQL queries. have a look at this.

Author concluded that "It seem to be consistent that the T-SQL query is performing better than the rest. The XML query FOR XML AUTO is using more than eight times more CPU but the same amount of I/O. The complex XML commands are issuing more than 80 times (!) more reads than the T-SQL command, also many writes and above six times the CPU."

Cheers

Ramesh Vel