INFO: Optimize XPath Queries Against Annotated XSD Schemas (813955)


The information in this article applies to:

  • SQLXML 3.0
  • SQLXML 2.6

SUMMARY

SQLXML is designed to generate sorted outer union (SOU) TSQL SELECT queries to execute XPath queries against annotated XSD schemas. SOU queries can be inherently inefficient when they are not optimized.

This article describes how to best optimize XPath queries when you execute these queries against annotated XSD schemas.

MORE INFORMATION

Use the following methods to execute XPath queries against annotated XSD schemas:

Method 1

Remove the CONVERT function in the query that is generated by SQLXML

The CONVERT function is used in conditional SELECT statements that are generated by SQLXML when explicit xsd or sql:datatypes (as required), or both, are not specified for the elements and attributes that map to columns in the target SQL Server table, and that are used the predicate of an XPath query. Using CONVERT functions affect the performance of the query adversely. It causes the query to use an index scan instead of an index seek (this assumes that this column is indexed as it must be). This can cause 10x degradation in performance.

Example
Annotated schema:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
            xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
  <xsd:element name="Customers" >
     <xsd:complexType>
       <xsd:attribute name="CustomerID"/> 
       <xsd:attribute name="ContactName"/> 
       <xsd:attribute name="Phone"/> 
     </xsd:complexType>
  </xsd:element>
</xsd:schema>
Xpath Query :
http://localhost/nwind/schema/Schema2.xml/Customers[@CustomerID="ALFKI"]
When you execute this XPath query, SQLXML generates the following SELECT statement (you can use SQL profiler to capture the query). Notice the use of the CONVERT function on the CustomerID column in the WHERE clause of the query. 
Select 1 as TAG,0 as parent,_Q1.A0 as [Customers!1!CustomerID],_Q1.A1 as [Customers!1!Phone],_Q1.A2 as [Customers!1!ContactName] from (select _QB0.CustomerID AS A0,_QB0.Phone AS A1,_QB0.ContactName AS A2,_QB0.ContactName AS _TBIJFFHBCA,_QB0.Phone AS _TBIHCFHBCA,_QB0.CustomerID AS _TBIIEFHBCA from Customers _QB0) _Q1 WHERE _Q1.A0 IS NOT NULL AND (_Q1.A0 = N'ALFKI') for xml explicit, binary base64
Microsoft recommends that you explicitly specify the data types of the elements and attributes that map to database table columns, in the annotated AXSD. The following is a modified version of the sample schema that applies this practice: 
<xsd:element name="Customers" >
     <xsd:complexType>
       <xsd:attribute name="CustomerID"  type="xsd:string" sql:datatype="uniqueidentifier"/> 
       <xsd:attribute name="ContactName" type="xsd:string" /> 
       <xsd:attribute name="Phone"       type="xsd:string" /> 
     </xsd:complexType>
  </xsd:element>
</xsd:schema>
When you execute the same sample XPath query against this annotated schema, the following TSQL SELECT statement is generated. Notice that the CONVERT function is now no longer applied to the CustomerID column in the WHERE clause: 
select 1 as TAG,0 as parent,_Q1.A0 as [Customers!1!CustomerID],_Q1.A1 as [Customers!1!Phone],_Q1.A2 as [Customers!1!ContactName] from (select _QB0.CustomerID AS A0,_QB0.Phone AS A1,_QB0.ContactName AS A2,_QB0.ContactName AS _TBIJFFHBCA,_QB0.Phone AS _TBIHCFHBCA,_QB0.CustomerID AS _TBIIEFHBCA from Customers _QB0) _Q1 WHERE _Q1.A0 IS NOT NULL AND (_Q1.A0 = N'ALFKI') for xml explicit, binary base64

Method 2

XML data can be attribute centric or element centric. Some of the generic XML findings suggest that attribute centric XML performs better than element centric XML. This may not be the case for all XML use cases, and requires scenario specific testing to confirm its applicability.

When you use element centric mapping in the context of SQLXML XPath queries against annotated schemas, each element requires a separate select and a sort key in the generated SOU SELECT statement. In some cases, the generated SQL is optimized by using !element option, but this is not guaranteed to always occur. As a result, attribute centric mapping yields better performance than element centric mapping when you execute XPath queries against annotated schemas.

The following are element and attribute centric versions of an annotated schema that maps to the Customers table in the SQL Server Northwind sample database:

Attribute centric
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
  xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="Customers" >
     <xsd:complexType>
                   <xsd:attribute name="CustomerID"  type="xsd:string" sql:datatype="uniqueidentifier"/> 
                   <xsd:attribute name="ContactName" type="xsd:string" /> 
                   <xsd:attribute name="Phone"       type="xsd:string" /> 
     </xsd:complexType>
 </xsd:element>
</xsd:schema>


Element centric 
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
  xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="Customers" >
     <xsd:complexType>
                   <xsd:element name="CustomerID"  type="xsd:string" sql:datatype="uniqueidentifier"/> 
                   <xsd:element name="ContactName" type="xsd:string" /> 
                   <xsd:element name="Phone"       type="xsd:string" /> 
     </xsd:complexType>
 </xsd:element>
</xsd:schema>

Method 3

Parameterized XPath queries against annotated schemas perform faster than non-parameterized queries on subsequent executions. This is because SQL Server can cache and reuse query plans that are generated for parameterized queries.

Example:
You can can modify the following XPath query:
http://localhost/nwind/schema/Schema2.xml/Customers[@CustomerID='ALFKI']
to use the following parameters:
http://localhost/nwind/schema/Schema2.xml/Customers[@CustomerID=$param]?param=ALFKI

Method 4

XPath comparisons imply existence tests (for example, comparisons against NULL). When the column cannot be NULL, use the NOT NULL designation in the table definition, and SQL Server removes these comparisons.
Example: The following XPath query:
Customer[@CustomerID = 'ALFKI']
generates a where clause that is similar to the following:
WHERE CustomerID = N'ALFKI'  AND CustomerID IS NOT NULL
If the CustomerID column is marked as NOT NULL in the table definition, SQL Server can remove the second test.

Method 5

The overall query performance is faster and better optimized when the following are true:
  1. All joins use an index (this assumes that the index is selective).
  2. The number of selects is as few as possible with the minimum target being equal to the number of underlying tables being accessed.
  3. Database columns that map to XML elements and attributes that are used in predicates in the XPath query are indexed.
Note The column aliasing and the size of the SQL query have no significant effect on performance. Performance depends on the efficiency of the query plan that SQL Server generates.

REFERENCES

For additional information, see the following article on the Microsoft Developer Network (MSDN) Web site:

Optimizing SQLXML Performance


Modification Type:MinorLast Reviewed:8/7/2003
Keywords:KB813955 kbAudDeveloper kbAudITPRO
©2003 Microsoft Corporation. All rights reserved.