Optimizing XML In TSQL

Question

I have recently started working with XML in table columns using TSQL, and found that it is horrendously slower than your average everyday 3rd-Normal-Form-associative-tables-Query-joining, from my tests conducted with 1.5 million rows, I mean it is 8 times slower than 3rd-Normal-Form-associative-tables-Query-joining. The upside is that with XML, if set up in an specific way, it prevents you from setting up extra associative tables (in my tests, my associative tables (join tables for many=many relationships got to 7.5 million rows in size, in comparison to my table with the XML which was 1.5 million rows in size storing the same information). Don't mind my ranting though, this was only tests on 2 sets of tables which I have performed.

Question

Its kind of hard to explain for me, as I am just a beginner-intermediate with SQL Server. What is the best practices for using Untyped XML in your columns, and how can you optimize the tables containing this XML to perform at optimal speeds, seeing as I must now deal with XPath and XQuery withing SQL Scripts now (which seems to be the cause of the severe speed slowdown according to execution plans)?

Answer 1

There are several things you can do to improve the performance of xml fields

• Add a primary XML index
• Add Secondary index
• Use an XML Schema
• Create a calculated field that extracts out data from the XML

XML fields tend to be slightly less IO intensive but much more CPU intensive. Check if that is an issue