asked
XML update
Hi , 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. code below Update PX_ags Set SumWatchList = (Select distinct WatchListID,WatchListName from (Select WL.WatchListID,WL.WatchListName From PX_WS WS INNER JOIN PX_WL WL ON WL.WatchListID = WS.WatchListID INNER JOIN m on WS.SecurityID =M.SecurityID INNER JOIN PX_A a on a.MeetingID = m.MeetingID INNER JOIN PX_B b ON a.AgendaId = b.AgendaId INNER JOIN dbo.vw_AccountPolicy_withDefault ap on b.AccountID = ap.AccountID and WL.CustomerID = AP.CustomerID WHERE WL.ForAllAccounts = 1 and px_ags.AccountGroupID = ap.v_VotingGroupID and b.LogicalBallotID = px_ags.LogicalBallotID UNION SELECT DISTINCT WS.WatchListID,WL.WatchListName FROM PX_WLS WS INNER JOIN PX_WL WL ON WL.WatchListID = WS.WatchListID INNER JOIN PX_WLA WA ON WA.WatchListID=WL.WatchListID INNER JOIN PX_M m on WS.SecurityID =M.SecurityID INNER JOIN PX_A a on a.MeetingID = m.MeetingID INNER JOIN PX_B b ON a.AgendaId = b.AgendaId INNER JOIN dbo.vw_AccountPolicy_withDefault ap on b.AccountID = ap.AccountID and WL.CustomerID = AP.CustomerID AND AP.AccountID = WA.AccountID WHERE WA.AccountID = B.AccountID AND WL.CustomerID = AP.CustomerID AND WS.SecurityID = M.SecurityID AND WL.ForAllAccounts = 0 AND px_ags.AccountGroupID = ap.v_VotingGroupID and b.LogicalBallotID = px_ags.LogicalBallotID ) t FOR XML PATH('WatchList')) from PX_M m inner join PX_A a on m.MeetingID = a.MeetingID INNER JOIN PX_LB on PX_LB.AgendaID = a.AgendaID where MeetingDate >= GETDATE()
@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? INNER JOIN m on WS.SecurityID =M.SecurityID vs INNER JOIN PX_M m ON WS.SecurityID = M.SecurityID 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. INNER JOIN PX_A a ON a.MeetingID = m.MeetingID INNER JOIN PX_B b ON a.AgendaId = b.AgendaId INNER JOIN dbo.vw_AccountPolicy_withDefault ap ON b.AccountID = ap.AccountID AND WL.CustomerID = AP.CustomerID 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.
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
3 People are following this question.
