Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have recently been researching Selective Xml Indexing and have been trying to figure out how to index a .query method

If I have the below query

WITH XMLNAMESPACES (Default 'http://www.w3.org/2001/XMLSchema' )
SELECT 

    ,a.value('@Number' , 'INT') As Number
    ,c.query('.') as FormattedXml

    From

    dbo.XmlTable As X

    Cross Apply x.XmlColumn.nodes('Root') aa(a) 
    Cross Apply a.nodes('Block') bb(b)

I can write a selective Index as below if I am following it right:

CREATE SELECTIVE XML INDEX xsi_XmlTable
ON dbo.XmlTable( XmlColumn )
WITH XMLNAMESPACES ( Default 'http://www.w3.org/2001/XMLSchema' )
FOR
(
      root = '/Root' AS XQUERY 'node()'
    , rootNumber = '/Root/@Number' AS SQL INT SINGLETON

)
GO

Can I include the c.query in the index? Thanks

share|improve this question
add comment

migrated from stackoverflow.com May 7 '13 at 14:34

This question came from our site for professional and enthusiast programmers.

1 Answer

up vote 4 down vote accepted

A selective index will not be used when using query to retrieve the data.

From Selective XML Indexes (SXI) - Supported XML Features

Selective XML indexes support the XQuery supported by SQL Server inside the exist(), value() and nodes() methods.

For the query() and modify() methods, selective XML indexes may be used for node filtering only.

For the query() method, selective XML indexes are not used to retrieve results.

However testing shows that the index is used when you rewrite the query using nodes. These queries returns the same but the second is faster because it uses the the selective XML index.

select data.query('/Log/CallStack/ExceptionThread/Line')
from LogData


select T.X.query('.')
from LogData
  cross apply data.nodes('/Log/CallStack/ExceptionThread/Line') as T(X)

The selective XML index used in the second query is defined as..

[Line] = '/Log/CallStack/ExceptionThread/Line' as XQUERY 'node()', 

I used a table of my own just to be able to show what the difference in this case is.

(67992 row(s) affected)
Table 'LogData'. Scan count 1, logical reads 388, physical reads 0, read-ahead reads 0, lob logical reads 1840865, lob physical reads 313514, lob read-ahead reads 24.

 SQL Server Execution Times:
   CPU time = 129792 ms,  elapsed time = 180087 ms.

(1850106 row(s) affected)
Table 'xml_sxi_table_622625261_256000'. Scan count 1, logical reads 8416, physical reads 3, read-ahead reads 8403, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'LogData'. Scan count 1, logical reads 388, physical reads 0, read-ahead reads 0, lob logical reads 927882, lob physical reads 0, lob read-ahead reads 24.

 SQL Server Execution Times:
   CPU time = 65271 ms,  elapsed time = 67636 ms.
share|improve this answer
    
Thanks for the reply! –  KeyboardFriendly May 7 '13 at 15:54
    
@KeyboardFriendly You are welcome. I updated with some statistics from the queries in the answer. –  Mikael Eriksson May 7 '13 at 16:08
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.