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
      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
    (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?

  • 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). Commented Feb 13, 2013 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. Commented Feb 13, 2013 at 23:32
  • That all being said... I suspect you already answered my question. Commented Feb 13, 2013 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
    Commented Feb 14, 2013 at 20:07
  • essentially, maybe you should be looking into a data warehouse. en.wikipedia.org/wiki/Data_warehouse
    – DForck42
    Commented Feb 14, 2013 at 20:09

1 Answer 1


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.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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