I have a table with lots of large xml-documents.

When I run xpath expressions to select data from those documents I run into a peculiar performance issue.

My query is

p.n.value('.', 'int') AS PurchaseOrderID
FROM XmlLoadData x
CROSS APPLY x.PayLoad.nodes('declare namespace NS="http://schemas.datacontract.org/2004/07/XmlDbPerfTest"; 
/NS:ProductAndRelated[1]/NS:Product[1]/NS:PurchaseOrderDetails[1]/NS:PurchaseOrderDetail/NS:PurchaseOrderID[1]') p(n)

The query takes 2 minutes and 8 seconds.

When I remove the [1] parts of the single occurance nodes like this:

p.n.value('.', 'int') AS PurchaseOrderID
FROM XmlLoadData x
CROSS APPLY x.PayLoad.nodes('declare namespace NS="http://schemas.datacontract.org/2004/07/XmlDbPerfTest"; 
/NS:ProductAndRelated/NS:Product/NS:PurchaseOrderDetails/NS:PurchaseOrderDetail/NS:PurchaseOrderID') p(n)

The execution time drops to just 18 seconds.
Since the [1]-nodes occurs just once in each parent node in the documents the results are the same except for ordering.

Actual execution plan for the first (slow) query is Plan for query 1

and the second (faster) query is

enter image description here

Query 1 full screen Query 2 full screen.

As far as I can see the query with [1] does the same execution as the query without, but with the addition of some extra calculation steps to find the first item.

My question is why the second query is faster.
I would have expected the execution of the query with [1] to break early when a match was found and thus reduce the execution time instead of the opposite.
Are there any reasons why the execution does not break early with [1] and thus reduce the execution time.

This is my table

CREATE TABLE [dbo].[XmlLoadData](
    [ProductID] [int] NOT NULL,
    [PayLoad] [xml] NOT NULL,
    [Size]  AS (len(CONVERT([nvarchar](max),[PayLoad],0))),
    [ProductID] ASC

Performance numbers from SQL Profiler:

Query 1:

CPU     Reads   Writes  Duration 
126251  1224892 0       129797

Query 2:

CPU     Reads   Writes  Duration 
50124   612499  0       16307
  • 1
    Please don't post a picture of the execution plan, but the plan. Meaning the XML file. There is a lot of information in those XML files that is not displayed in the picture of the plan. Jan 6, 2012 at 1:20

3 Answers 3


The second query uses parallelism. That is, it was expensive enough for the optimizer to shut its eyes to the additional overhead.

I'd guess the second query tells optimizer to "dump everything", which is performed with a paralleled scan. SQL Server likes to "dump everything" in this way when asked.
Whereas the first query asks for "analyze and then give some." The optimizer has no way of knowing there's only one node anyway, so the execution plan it ends up picking is very different.

I'd say it's similar to situation when one table scan is cheaper than many index seeks.

  • You are right in that the second query uses parallelism. But I can't figure out how that could account to such big difference on my quad core machine. I edited the question with numbers from the SQL profiler, the total CPU time spent is more than 50% lower on the second query anyway. Is the lowered CPU consumption an effect of that the parallel query plan happens to be more efficient too in addition to be parallel? Nov 27, 2011 at 17:12

Have a look at Performance Optimizations for the XML Data Type in SQL Server 2005 and the section about "Moving Ordinals to the End of Paths".

Ordinals used in path expressions for static type correctness are good candidates for placement at the end of path expressions. The path expression /book[1]/title[1] is equivalent to (/book/title)[1] if every <book> element has <title> children. The latter can be evaluated faster for both the XML indexed case and the XML blob case by determining the first <title> element under a <book> element in document order. Similarly, the path expression (/book/@ISBN)[1] yields faster execution than /book[1]/@ISBN.


Querying XML is a beast. Adding XML indexes to your table will make the queries a LOT faster.

  • I'm planning to running those queries once to build relational tables containing the data I want to search for. The reason I need speed is that we want to minimize the disturbances in our production database when we extract the data. Building an XML index takes a lot of time too. Nov 27, 2011 at 21:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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