Performance issue in retriving data from XMLAttrib

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

Transact-SQL (2005)

Performance issue in retriving data from XMLAttrib

Author

Topic

sadiqmodan
Starting Member

16 Posts

Posted - 2012-09-10 : 07:33:36

down vote favorite

I have one history table which has information of transaction record of master table, in this table I have used XML column to store that transaction information. Table structure with data look like.

Table information
id |content | createdDate |nodeid
----------------------------------------------------
1 <answerset.. 2012-08-07 04:59:15.550 10
2 <answerset.. 2012-08-07 03:00:00.420 10
3 <answerset.. 2012-08-07 05:00:15.550 11
4 <answerset.. 2012-08-06 03:15:00.420 10
5 <answerset.. 2012-08-07 04:59:15.550 12
6 <answerset.. 2012-08-07 04:59:15.550 11

In Content XML data are stored as xml like below.

<Answers>
      <AnswerSet>
        <Answer questionId="ProductCode">S3404</Answer>
        <Answer questionId="ProductName">Parabolic Triple</Answer>
        <Answer questionId="LegacyOptionID" selectedvalue="1389">1389</Answer>
        <Answer questionId="LegacyContentID" selectedvalue="624">624</Answer>
        <Answer questionId="LegacyPageID" selectedvalue="355">355</Answer>
        <Answer questionId="LegacyParentID" selectedvalue="760">760</Answer>
     </AnswerSet>
</Answers>

In all rows structure is same but data is different in answer node, I want to get data which has ProductCode="S3404" and CreatedDate is New.

I have created query like

select n2.* from nodehistory n2 CROSS APPLY n2.content.nodes('Answers/AnswerSet') T(c) WHERE c.value('./Answer[@questionId="ProductCode"][1]','varchar(100)') ='J154'

ProductCode have unique data for every nodeid, but this is returning more than one row for same nodeid because this is transaction table so same XML can be store multiple time, for this require condition like order by Createddate desc, but execution of this query is taking more time due to XML processing I think.

Can we do like first get Select Top 1 nodeid from NodeHistory order by CreatedDate desc then search for XML part.

We have created index for Content field.

Please suggest you suitable views for better performance

Thanks,
Mohmedsadiq

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-10 : 09:44:04

First, verify that querying the XML is what is taking the time (if your query is any more complex than what you showed). You can do this by looking at the query plan and looking for steps such as Table Value Function [XML Reader].

I may not have understood your description well enough, but it seems to me that if you are looking for the first AnswerSet node then you can change your query to one of the following (or may be both)

select n2.* from nodehistory n2 CROSS APPLY n2.content.nodes('Answers/AnswerSet[1]') T(c) 
WHERE c.value('./Answer[@questionId="ProductCode"][1]','varchar(100)') ='J154'

or perhaps,

;with cte as
( SELECT n2.*,ROW_NUMBER() OVER (PARTITION BY nodeid ORDER BY createDate DESC) AS RN
  FROM nodehistory n2
)
select n2.* from cte n2 CROSS APPLY n2.content.nodes('Answers/AnswerSet') T(c) 
WHERE c.value('./Answer[@questionId="ProductCode"][1]','varchar(100)') ='J154'
AND RN = 1;

Also, I assume you created a primary XML index. You could consider creating a PATH secondary index, but as with all XML indexes, it takes up space and slows down udpates/insertions, so use that as the last option.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources