Should use XML indexes or not?

Question

Should use XML indexes or not?

Bhavesh-1094084

SSC Eights!

Points: 865
More actions
August 12, 2009 at 5:37 am

#134757

I'm using XML datatype column in one of my table which has following structure

Data in this column grows hugely (most cases). I am writing following query to get data out from XML as dataset
SELECT R.nref.value('@ExpressionRef[1]', 'INT') AS ExpressionRef,
R.nref.value('@ErrorResult[1]', 'VARCHAR(50)') AS ErrorResult,
X.rref.value('@Ref[1]', 'BIGINT') RowRef,
X.rref.value('@TimeStamp[1]', 'DATETIME') [TimeStamp]
FROM @XMLDataState.nodes('//Log') AS R ( nref )
CROSS APPLY R.nref.nodes('DataRows/Row') X ( rref )
WHERE X.rref.value('@TimeStamp[1]', 'DATETIME') BETWEEN @StartTimeStamp
AND @EndTimeStamp
OR ( @StartTimeStamp IS NULL
AND @EndTimeStamp IS NULL
)
AND (R.nref.value('@ExpressionRef[1]', 'INT') = @ExpressionRef OR @ExpressionRef IS Null)
In this case should I create primary index on XML column and secondary indexes (not sure which on either FOR PATH, VALUE or PROPERTY?) then use exists() to look for filter values
OR
Would the above query will give me better performance?
Bhavesh
.NET and SQL Server Blog

Viewing 3 posts - 1 through 2 (of 2 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Why don't you test both options and see which is better? Offhand, I can't say which'll be faster.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Bhavesh-1094084 SSC Eights! Points: 865 More actions · Answer 2

GilaMonster (8/12/2009)
Why don't you test both options and see which is better? Offhand, I can't say which'll be faster.

Thanks will do it

Bhavesh
.NET and SQL Server Blog