just started a new role and I have a proc and a trigger which updates an XML column in a table.These are really performing poorly. I dont know why it was done like this but it is..
The XML update is based on an ID column which is indexed, however I cant create a composite index with the XML column so I believe it is scanning for every update.
the xml update is done by extracting the data out , converting it and then putting back into xml , this is pretty poor
Any ideas how this can be improved.
Update PX_ags Set SumWatchList = (Select distinct WatchListID,WatchListName from (Select WL.WatchListID,WL.WatchListName From PX_WS WS INNER JOIN PX_WL WL
where MeetingDate >= GETDATE()
asked Mar 05 '12 at 11:26 AM in Default
@Gergav38 This seems to be a long query. Without knowing the logic, the data structures involved, execution plan we cannot help much. The best thing you can do is to re-design your query according to the logic you have.
One thing noticed is that in the sub-query, you have one instance of m (PX_M) a reference from the main query and another instance of PX_M m as a new reference? Is it the requirement of the logic?
Another thing noticed is that, there is quite redundant work going on. There are quite a few joins which could be called just once. May be in the Main query.
But since I do not know the logic, it is all guesswork and may not yield the exact results. But try to keep the number of joins as least as possible, so the query optimizer could can generate an optimal plan.
answered Mar 05 '12 at 01:08 PM
thanks Usman , I can worrk on the joins but I was wondering is there a more efficient way of updating the xml rather than the cast / convert method
answered Mar 05 '12 at 01:15 PM