XML update

Question

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

Answer 1

@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.

Answer 2

0

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

Gergav38
62 ● 1 ● 1 ● 5

(comments are locked)

XML update

Follow this question

Related Questions