Index XML column - slower response!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Index XML column - slower response!

Author

Topic

edvin
Starting Member

5 Posts

Posted - 2008-12-29 : 17:21:14

Greetings,

Has anyone experimented with indexing XML columns?

I seem to get a factor of 10 slower response (querying an element of xml) after adding Primary XML index! Secondary index does not help much either.

-- CREATE PRIMARY XML INDEX idxPrim_tblXml ON tblXml( xml );
-- CREATE XML INDEX idx2ndValue_tblXml ON tblXml (xml) USING XML INDEX idxPrim_tblXml FOR VALUE ;

SELECT XML.value('(/Users/ssnPSSN)[1]', 'varchar(11)')
FROM tblXml
WHERE XML.value('(/Users/ssnPSSN)[1]', 'varchar(11)') = '555455446'

-- Fictitious SSN

tnx for help in advance

edvin
Starting Member

5 Posts

Posted - 2008-12-31 : 15:15:51

According to Microsoft, this is a Known BUG in SqlServer 2005, and it is addressed in SqlServer 2008.

That means, you need to either upgrade or implement a workaround.

-Edvin

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-31 : 18:21:09

Do you have a link to the documentation of the known bug?

E 12°55'05.63"
N 56°04'39.26"

edvin
Starting Member

5 Posts

Posted - 2009-01-02 : 14:12:03

quote:
Originally posted by Peso

Do you have a link to the documentation of the known bug?

E 12°55'05.63"
N 56°04'39.26"

No, i don't have a link regarding the XML index bug in SqlServer 2005.
I contacted Microsoft Support Directly, and they informed me of the defect.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-02 : 14:17:07

quote:
Originally posted by edvin

quote:
Originally posted by Peso

Do you have a link to the documentation of the known bug?

E 12°55'05.63"
N 56°04'39.26"

No, i don't have a link regarding the XML index bug in SqlServer 2005.
I contacted Microsoft Support Directly, and they informed me of the defect.

Can you elaborate on this so anyone facing same problem will get knowledge?

edvin
Starting Member

5 Posts

Posted - 2009-01-02 : 17:09:16

As you are aware, SQLserver 2005 introduced XML type columns.

Furthermore, we can utilize XQuery to extract individual data elements from the xml column. For example, given the below XML
<Users>
<ssnPSSN>111111111</ssnPSSN>
<ssnPSSN>222222222</ssnPSSN>
<ssnPSSN>333333333</ssnPSSN>
</Users>

And the following SQL

SELECT myXmlCol.value('(/Users/ssnPSSN)[2]', 'varchar(9)') FROM tblXml

Would result in 222222222

In my case, I don’t have multiple, instances of a field. That is, my xml column contains detailed information of a single user (name, address, phone, etc).
We needed a way to query for specific record by filtering on XML column (say ssn of the user).

XML index seemed like an appropriate solutions at the time. However, as reported earlier, adding an XML indexes in SqlServer 2005 will yield longer response times.

In particularly, I observed three times delay in response time. That is, querying a sample table with 200,000 records yielded 20 second respond without an index. After adding XML index, the response time was 1:30 sec.

Furthermore, in “Sql Server Profiler”, I observed a factor of 10 increase (from 25 to 250) in number of “Reads” and a twenty percent increase in “Cpu” (from 20 to 25).

For those that are interested in exploring XML indexes; keep the following in mind:
“The number of rows in the index is approximately equal to the number of nodes in the XML binary large object” (MS whitepaper).

Why use XML and index on it? That is a different discussion.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2009-01-02 : 17:12:31

this didn't really help anyone. i think what we'd like to know is what PSS person told you that the origin of the problem was?

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.1 out!

edvin
Starting Member

5 Posts

Posted - 2009-01-02 : 18:10:57

What is PSS? (perfromance sql support)!

Here is what I got from Microsoft Sql Server Performance Support Engineer:

quote:

CAUSE:

Due to SQL BU Defect Tracking 504821 - Performance of select with XML column and XML indices is slower than if there are no XML indices

RESOLUTION:

No fixes for SQL 2005

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2009-01-02 : 18:21:11

PSS = Microsoft Product Support Services

that's all the explanation you got? weird... thanx.

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.1 out!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources