performance tune update sql

Hi All,

Can any one tell me what i am doing wrong here , the following update for millions of records takes hell lot of time.
How can i make this update faster.
Thanks in advance.


 SET     PPO = ROUND(100 * ST.PPO, 2) ,
         PSO = ROUND(100 * ST.PSO, 2) ,
         KPO = ROUND(100 * ST.KPO, 2) ,
         KSO = ROUND(100 * ST.KSO, 2)
                              ELSE 0
                         END) PPO ,
                     MAX(CASE WHEN COUNTRYID = 3 THEN SCOPE
                              ELSE 0
                         END) PSO ,
                     MAX(CASE WHEN COUNTRYID = 4 THEN TOTAL
                              ELSE 0
                         END) KPO ,
                     MAX(CASE WHEN COUNTRYID = 5 THEN VALUE
                              ELSE 0
                         END) KSO
              FROM   TABLEA
              INNER JOIN COUNTRY
                        AND COUNTRYID IN ( 1, 3, 4, 5 )
            ) ST
         ON TABLEA.ID = ST.ID
more ▼

asked Oct 13, 2012 at 06:04 PM in Default

avatar image

2.8k 56 65 71

i forgot to mention that my update is done in batches of 50000

Oct 13, 2012 at 06:12 PM aRookieBIdev

Are there any update triggers on the target table?

How's the query optimiser running the JOIN? Can you show the Query Plan for an update run of, say, 1000 records?

Oct 13, 2012 at 06:20 PM ThomasRushton ♦♦

I agree with Thomas that if you can provide the query plan that will definitely.

How long is "hell lot of time" and how long does it take to update 50K rows? Is there anything else running on the server at the same time? Any fragmented indexes? How many indexes on TableA? Statistics updated? Can you lower that batch number?

Another thought, you could perform the select with the case statements into a temp table and then use that to join against in the update. This would depend on how large of a data set that second select would return. Just a thought.

Oh yea, what version of SQL Server?

Sorry for all of the questions. ;-)

Oct 13, 2012 at 07:34 PM JohnM

I am using sql server 2008 . I tried to copy the execution plan but i couldnt take it from the remote server.

The table scan takes 56 % and the update takes 41 %

Oct 13, 2012 at 07:43 PM aRookieBIdev
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

What I would try:

  1. Move the creation of the Pivot table into it's own step. Throw it into a temp table or work table (#myTemp). Index it on ID.

  2. Do the Rounding work as you are creating the pivoted work table (#myTemp).

  3. Try batches of 1000. These will be moderately kinder to your transaction log and may help if you're hitting a hardware limit.

more ▼

answered Oct 13, 2012 at 07:26 PM

avatar image

90 1 3 6

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

I agree with @willspurgeon, making the INNER SELECT a step in its own right to have the data in a temp table would be my choice the try next for an improvement. I'd also look to move the COUNTRYID filter into a WHERE predicate rather than in the JOIN.

Are there many indexes on TABLEA? updates will have to update all indexes on this table too. If it is millions of rows deep and you are updating a lots of indexes then it will be slow. You may need to consider dropping the indexes while the update is actioned and then re create them after the work is done.

The DDL of TABLEA would help us a lot

more ▼

answered Oct 14, 2012 at 10:07 AM

avatar image

Fatherjack ♦♦
43.8k 79 102 118

(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



Answers and Comments

SQL Server Central

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



asked: Oct 13, 2012 at 06:04 PM

Seen: 858 times

Last Updated: Oct 14, 2012 at 10:07 AM

Copyright 2018 Redgate Software. Privacy Policy