Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table that contains XML data in a varchar type field. This field is used to store data from a variety of different XML schemas some of which are related via a record stored in another table.

I recently created some views which parse the XML and in some cases make a pivot of some of the values. The views provide excellent data for my reports but come with a serious performance hit. I'd like to find out if I can improve performance with indices.

Here is an example of a view:

create view StudentHours as
  select
      x.TableRecordId as Id
    , x.RecordXml.value('(/TableRecord/LOGDate)[1]', 'DateTime') as LogDate 
    , x.RecordXml.value('(/TableRecord/LOGHours)[1]', 'float') as Hours
    , x.RecordXml.value('(/TableRecord/LOGApproved)[1]', 'varchar(10)') as Approved
    , y.PKTableRecordId as CourseId
  from
    (select TableRecordId, Cast([Schema] as Xml) as RecordXml 
     from TableRecords where TableSchemaId = 1857) as x
  join TableRecordRelations as y on x.TableRecordId = y.FKTableRecordId

I then will use this view in other views which do aggregates and such.

The table called TableRecords has an index on its unique id TableRecordId and the TableRecordRelations also has indices on its important fields as well.

Will adding an index or two help this view's performance? Is more data needed to determine this?

share|improve this question
4  
An indexed view is not a "go faster" button. Indexed views are costly to maintain and are typically reserved for situations where you're performing aggregates (performing aggregates over a non-aggregated indexed view will not necessarily be any better than over the base table(s)). Have you considered using the right data type (XML) and using XML indexes? You might also benefit from advances in this area beyond SQL Server 2005 (which is no longer supported). –  Aaron Bertrand Feb 13 '13 at 23:13
    
I'd love to use the right datatype but that's a significant development overhead on the application side that the bosses are unwilling to commit to right now. Same applies to moving beyond SQL Server 2005. I do perform aggregates in other views which join on this view which is why I was hoping to find some performance gains somewhere along the way. –  Sailing Judo Feb 13 '13 at 23:32
    
That all being said... I suspect you already answered my question. –  Sailing Judo Feb 13 '13 at 23:33
    
a tangential approach may be creating an etl process to cache the data. how often does the data change, what's the need for the data to be up to date? things along those lines are necessary to ask. –  DForck42 Feb 14 '13 at 20:07
    
essentially, maybe you should be looking into a data warehouse. en.wikipedia.org/wiki/Data_warehouse –  DForck42 Feb 14 '13 at 20:09
add comment

1 Answer

up vote 4 down vote accepted

I believe that the cast to XML is killing performance for you and what is happening is described by Paul White Compute Scalars, Expressions and Execution Plan Performance

The cast to XML is deferred to where you actually use the XML column so in your query the cast happens three times for each row returned.

share|improve this answer
2  
also looked at in this answer –  Martin Smith Feb 14 '13 at 9:18
    
thanks for this... I'll look into it more. –  Sailing Judo Feb 14 '13 at 23:22
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.