I have spent the last three years developing a multi-server, multi-database plat formed system, using similar techniques. We have had various performance penalties, most of which were unacceptable and have been re-engineered. The worse penalties from the use of XML are network bandwidth, SQL IO and all of the conversion time to and from XML. There is no real support for XML, (in SQL 2000 and not much more in 2005), and it is not as efficient as any other well defined proprietary/in-house data structure for transferring data to and from SQL or message queues.
The performance penalties were not apparent during the first year, because we were using test data, not real production size recordsets or user counts. The second year was painful as larger customers came online with growing recordset and user counts, (>2GB databases and >20 users). We suffered response times, blew out message queues and reached .Net Grid limits using too much XML. For the larger records we eventually replaced the XML with our own data structure, increasing our performance 4 fold while allowing for easier troubleshooting. We have some records that were larger than 10MB of XML, (some as large as 50MB).
What really happens when you update a record that contains XML and you are only updating the XML? You write back the entire XML. I have not found anything that actually updates the virtual ‘column’ in the XML. This may not be a problem when you update 50 bytes and the total XML is small/ 10K. But try it when the XML is 100K+ and a 50 byte update performance hit is felt.
· Use a temp table to share data between stored procedures on the same connection/process.
· XML does not scale up, it s l o w s d o w n .
· Use the SQL database as it was intended, for storage, selecting out to XML at the end if you application needs it. A database inside a database makes sense? NOT!
· Storing large XML records or recordsets in a table, it is not efficient; break it out to columns or multiple related tables.
· XML in SQL 2000 or 2005 is not fully supported and lacking utilities/tools
· Definitely no large file/record support (i.e. try loading 100-500MB of XML and working with it, or load corrupt XML for repair)
· If you use XML, keep is small and for commands or configuration/attributes to enhance a process, not EDI like transfers of data.
[font="Arial"]Clifton G. Collins III[/font]