x

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

asked Mar 05, 2012 at 11:26 AM in Default

avatar image

Gergav38
82 7 7 11

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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

more ▼

answered Mar 05, 2012 at 01:08 PM

avatar image

Usman Butt
14.3k 6 13 21

(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Mar 05, 2012 at 01:15 PM

avatar image

Gergav38
82 7 7 11

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x204

asked: Mar 05, 2012 at 11:26 AM

Seen: 650 times

Last Updated: Mar 05, 2012 at 01:15 PM

Copyright 2018 Redgate Software. Privacy Policy