Unexplicable performance degradation with typed XML
In the repro below, I assign the same XML document to three different XML variables. One is untyped, one is typed with the values as strings and one with the values as integers. I then measure the time it takes to extract the values from the documents. I find that using typed XML is the fastest way. To extract the values from the document where the value is declared as integers takes more than double that time.
This is highly surprising. Rather I would expect the correctly typed document to be faster, since the values could be stored as integers internally, and thus there would be no cost for conversion. I have no idea what is going on, but I get the feeling that SQL Server is once again validating that the document adhers to the schema.
Upvotes: 3<=-=Oct 9 2010 3:25AM=-=>
To reproduce the problem using Erland’s script the Numbers table should exist in the same database.
The Numbers table can be created using the following script (from the Arrays and Lists in SQL Server 2005 article by Erland Sommarskog):
Thanks for the report. We are investigating why this is happening. It may be that we have an additional cast from xs:int to string “back” to SQL int as part of implementing the value method casting semantics.