I am currently using a SQL Server XML Schema Collection to maintain schemas for a variety of dynamic, custom data types. Over time it is expected that this schema collection would grow. I've discovered that as the schema collection grows, however, the performance of the XML value() function degrades.< xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />
The first time a query is executed that uses an XML value() function call, for a typed XML column or variable, a significant time delay is added for each call to the value() function. Additionally, this time delay is proportional to the number of schemas in the XML Schema Collection. For a schema collection with only one schema, the delay is less than one second for each value() call. For a collection with 96 schemas in it, the delay is about 5 seconds per value() call. For a schema collection with 191 schemas in it, the delay is about 24 seconds per value() call. Subsequent executions of the same query complete quickly. If any part of the query changes on subsequent invocations, however, the long delays return. I have tested this with both the June and September CTP of SQL Server 2005. I tested this with add-hoc queries, views, and stored procedures written in Transact-SQL. I have also tested this with XML columns that have all the special XML indexes defined. The results were always the same.
The first-time delays are a problem for my application because its queries are not constant (i.e. the parameter to the value() function will change dynamically). Also, a typical query in my application may require more than one call to the value() function.
Are these delays expected behavior Are there any optimizations that can be made to improve performance Are there any design alternatives other than not using typed XML
Any help would be appreciated.
A very simplified example of my query is listed below:
DECLARE @myVar xml(mySchemaCollection)
SET @myVar = '
<x:ns xmlns:x="urn:mySchema">
<value id="100">my value for 100</value>
<value id="101">my value for 101</value>
<value id="102">my value for 102</value>
</x:ns>'
SELECT @myVar.value('
declare namespace x="urn:mySchema";
(/x:ns/value[number(@id)=101])[1]', 'nvarchar(max)') As [MyValue]
Slow XML value() calls
seizonsha
The reason why the first execution takes longer is that we compile the XQuery expression. As part of that we also do static type inferencing based on the types provided by the XML Schema collection. We are aware that for complex schemas there are some performance issues that we are currently investigating. Unfortunately, this will not be fixed for RTM, but we are looking into it for a service pack. If you are blocked by this and need an earlier fix, please let me know (my alias is mrys at the standard domain
Some ways to avoid some of the compilation overhead is to avoid using // and wildcards in your path expressions and use sql:variable() to parameterize the values in a path expression.