Re: openxml question

Typo, 100 KB XML document, not 1 MB.

"Mike C#" <xyz@xxxxxxx> wrote in message
news:eLL1CpKUIHA.3400@xxxxxxxxxxxxxxxxxxxxxxx

"Marc Gravell" <marc.gravell@xxxxxxxxx> wrote in message
news:a6c9a102-6257-4947-b086-73c555aafe01@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On SQL2005 (!), I've used openxml to process two megabyte files
Simply out of curiosity, did this out-perform using the xml datatype
to do the inital shred?

Marc

I just ran a couple of tests on my server (SQL 2005, 2 GB RAM, 2.2 GHz).
On a simple 1 MB XML document I came up with the following performance:

- shredding an XML type variable with nodes() method 1,000 times took 77
seconds
- shredding a VARCHAR variable with OPENXML 1,000 times took 76 seconds
- shredding an XML type column with nodes() method and primary XML index
1,000 times took 42 seconds

OPENXML appears to be slightly faster for simple XML documents, although
it reserves 1/8th of the SQL Server memory regardless of the size of the
XML document. I didn't test it with more complex documents and paths,
like documents with multiple namespaces, and several levels of nesting,
etc. When you are shredding XML documents stored in a column, the nodes()
method with a primary XML index is considerably faster than either of the
other two methods.



.

Loading