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)', 'DateTime') as LogDate , x.RecordXml.value('(/TableRecord/LOGHours)', 'float') as Hours , x.RecordXml.value('(/TableRecord/LOGApproved)', '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?