x

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.

Kannan

UPDATE  BR
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)
FROM    TABLEA
INNER JOIN ( SELECT ID ,
                    MAX(CASE WHEN COUNTRYID = 1 THEN STRENGTH
                             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
                    ON TABLEA.COUNTRYID = COUNTRY.COUNTRYID
                       AND COUNTRYID IN ( 1, 3, 4, 5 )
           ) ST
        ON TABLEA.ID = ST.ID
more ▼

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

aRookieBIdev gravatar image

aRookieBIdev
2.3k 47 55 61

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

willspurgeon gravatar image

willspurgeon
90 1 1 3

(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

Fatherjack gravatar image

Fatherjack ♦♦
42.4k 75 78 108

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

x246
x129

asked: Oct 13, 2012 at 06:04 PM

Seen: 646 times

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