FOR XML vs Resultset | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

FOR XML vs Resultset

How much performance trade off should I expect to use FOR XML to generate XML string directly from SQL Server? I like this approach because it is simple and easy. But others told me that FOR XML has big performance hit. They prefer to use a COM+ component to create XML string from Resultset. What do you think?
Ask him for references; Ive not heard of SQL servers XML generator being particularly slow. However, returning XML from SQL server may increase network bandwidth to the client (or middle tier).
Also, if you return a result set to the middle tier, you are then faced with the prospect of writing a generic script which generates XML from any result set, or write a script specific to each result set (not very maintainable).
Unless you want your XML to be structured in a non default way, Id be very tempted to stick with SQL’s generator.
Ask him for references; Ive not heard of SQL servers XML generator being particularly slow. However, returning XML from SQL server may increase network bandwidth to the client (or middle tier).
Also, if you return a result set to the middle tier, you are then faced with the prospect of writing a generic script which generates XML from any result set, or write a script specific to each result set (not very maintainable).
Unless you want your XML to be structured in a non default way, Id be very tempted to stick with SQL’s generator.
The only problem I’ve come accross using FOR XML to return data in an xml format is as Chappy says the inflated size of the data over the network.
For recordsets containing about +-30 rows it performs great and is much easier than coding up your own custom xml formatter from a recordset but with larger records being returned the streaming from the ado recordset and network takes it’s toll and you might find you’d be better off using a forward only, read only recordset and create your own xml from it, it’s quite easy to create a generic routine to do this if you know your columns will either always be attributes or child nodes of the record. Cheers
Shaun World Domination Through Superior Software
]]>