Nov 30 2009

SQL Server XQuery For Idiots

I’m still struggling with learning XQuery. My latest little revelation was small, but vital.

The difference between:

@inXML.nodes('/rss/channel/item')

and

@inXML.nodes('/rss[1]/channel[1]/item')

Is the difference between a 10 minute query and a 4 second query. Kind of shocking really. My understanding is that XQuery assumes there are multiple possible paths that look like “/rss/channel” so it searches over and over again through the XML to see if it can find them. But by identifying it as a root, showing that only one possible path is available, it stops stumbling around in the dark and simply reads the data.

It’s a little thing, but it made an enormous difference. I’ve still got a long way to go in learning how to use XPath within XQuery.

UPDATED: I modified the title so that it’s clear I’m talking about SQL Server here.

6 Comments

  • By Brad Schulz, November 30, 2009 @ 1:30 pm

    Even worse is doing //rss/channel.

    At least /rss/channel will find rss nodes just under the root and then will look for all the channel nodes under those.

    But //rss/channel will find ALL rss nodes ANYWHERE in the document and then will look for all the channel nodes under those.

    And then there’s //rss//channel. You can guess what that does. Yeesh!

    I’ve seen a lot of “solutions” posted on the MSDN T-SQL forum that use the // construct… not a good idea with a large XML document.

    –Brad

  • By scarydba, November 30, 2009 @ 2:58 pm

    Oh, good catch. I knew about that problem as well. I should have put it in there. Thanks for outlining it so well.

  • By Michael Kay, December 1, 2009 @ 4:31 am

    A useful tip – but only for people who know which XQuery product you are using. A different XQuery implementation with a different optimizer might give completely different results, and the best way to write a query on one system is not necessarily the best way to write it on another.

    Frankly, this result suggests that your XQuery implementation is not particularly smart, so it would be interesting to know which it is. (From your tagline, might one assume SQL Server?)

  • By scarydba, December 1, 2009 @ 11:10 am

    You know, I hadn’t thought of that, but you’re right. I didn’t mention it was for SQL Server at all.

    And don’t judge SQL Server’s implementation of XQuery too harshly by what I’m writing. I’m more than a little bit dense on the topic, hence the tips for idiots, like myself.

  • By scarydba, December 1, 2009 @ 11:11 am

    Oh, and I’ll modify the title, just for the next person that stumbles across it.

  • By Raghuram (AJ), August 12, 2010 @ 2:10 pm

    Very useful tip and simple explanation! I have put this in practice.

    Thank you!

Other Links to this Post

RSS feed for comments on this post. TrackBack URI

Leave a comment