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

Gergav38 gravatar image

Gergav38
82 4 6 9

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

2 answers: sort oldest

@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

Usman Butt gravatar image

Usman Butt
13.9k 6 8 14

(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

Gergav38 gravatar image

Gergav38
82 4 6 9

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

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x150

asked: Mar 05, 2012 at 11:26 AM

Seen: 521 times

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