Sql-server – Adding indices to views

materialized-viewsql serversql-server-2005view

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?

Best Answer

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.

Related Question