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 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

SELECT
p.n.value('.', 'int') AS PurchaseOrderID
,x.ProductID 
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:

SELECT
p.n.value('.', 'int') AS PurchaseOrderID
,x.ProductID 
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))),
 CONSTRAINT [PK_XmlLoadData] PRIMARY KEY CLUSTERED 
(
    [ProductID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
       IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, 
       ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Edit:
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
share|improve this question

migrated from stackoverflow.com Nov 27 '11 at 16:30

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

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. –  Remus Rusanu Jan 6 '12 at 1:20
add comment

3 Answers 3

up vote 2 down vote accepted

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.

share|improve this answer
    
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? –  Albin Sunnanbo Nov 27 '11 at 17:12
add comment

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.

share|improve this answer
add comment

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

share|improve this answer
    
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. –  Albin Sunnanbo Nov 27 '11 at 21:06
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.