|
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. Kannan
(comments are locked)
|
|
What I would try:
(comments are locked)
|
|
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
(comments are locked)
|


i forgot to mention that my update is done in batches of 50000
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?
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. ;-)
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 %