question

aRookieBIdev avatar image
aRookieBIdev asked

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
performanceupdate
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image ThomasRushton ♦♦ commented ·
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?
2 Likes 2 ·
aRookieBIdev avatar image aRookieBIdev commented ·
i forgot to mention that my update is done in batches of 50000
0 Likes 0 ·
JohnM avatar image JohnM commented ·
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. ;-)
0 Likes 0 ·
aRookieBIdev avatar image aRookieBIdev commented ·
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 %
0 Likes 0 ·
willspurgeon avatar image
willspurgeon answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered
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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.