XQuery, output the integer that represents a singleton

Question

XQuery, output the integer that represents a singleton

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Adam Machanic SSCoach Points: 15370 More actions · Answer 1

I'm getting a very different plan than you are -- and a much more efficient one at that. The biggest difference? My plan uses only the primary XML index, and estimates are therefore much closer to reality (a few rows instead of a few hundred thousand). Can you try dropping the secondary XML indexes and see if that makes things faster? Have they been created only for this one query, or are you doing other XQuery against this data?

--
Adam Machanic
whoisactive

Matthew Darwin SSCertifiable Points: 5513 More actions · Answer 2

Just tried it, and had a massive improvement on performance - both queries ran in under 12 seconds.

The indexes were only created by me as I thought they'd help; there are a few other sections to shred, but I'll check through and make sure they're not adversely affected.

Once the file is shredded fully there's no need for it to be accessed again, so there shouldn't be any problem there.

Since starting typing this, I've run through the lot, and everything runs in seconds now, so a very big improvement.

So I know for future, do you have any inkling why the additional indexes caused a detriment in execution?

Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]

Adam Machanic SSCoach Points: 15370 More actions · Answer 3

Glad to hear that it worked!

Why did it cause a problem? Well, the fact is that XML indexes in SQL Server are really not well integrated with the query optimizer. They're good for a couple of VERY specific scenarios but in practice seem to cause a lot more problems than they solve. Apologies for a not very satisfying answer, but it's the unfortunate reality of the situation.

--
Adam Machanic
whoisactive

Matthew Darwin SSCertifiable Points: 5513 More actions · Answer 4

Ah I'll bear that in mind for the future! Thanks very much for all the help, it's been brilliant. 😀

Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]

WayneS SSC Guru Points: 95460 More actions · Answer 5

Adam Machanic (12/30/2010)
Glad to hear that it worked!
Why did it cause a problem? Well, the fact is that XML indexes in SQL Server are really not well integrated with the query optimizer. They're good for a couple of VERY specific scenarios but in practice seem to cause a lot more problems than they solve. Apologies for a not very satisfying answer, but it's the unfortunate reality of the situation.

Adam - would you then generically say that the primary XML index is "good", and not to worry about the secondary ones?

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2