Update in batches

Question

Post reply

Update in batches

ishaan99

SSCertifiable

Points: 6261
More actions
September 11, 2006 at 9:40 am

#114423

I am trying to do an update on a table which has approx 4 million rows. To avoid transaction log from blowing up I was thinking to run the update in batches. Does anyone have the code for this or have done something similar . Any help will be greatly appreciated.
TIA

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Dinakar Nethi-176633 SSCarpal Tunnel Points: 4689 More actions · Answer 1

You could do it in a batch. Also have a job running (perhaps every minute) to truncate the log.

******************
Dinakar Nethi
Life is short. Enjoy it.
******************

ishaan99 SSCertifiable Points: 6261 More actions · Answer 2

Sorry but i cant truncate the log when thsi needs to be run in a prodn environment. Also I was looking if someone has written a code for this before.

TIA

Sreejith Sreedharan SSCertifiable Points: 5403 More actions · Answer 3

Does ur table have an identity column or datetime column that can be used. If so u can easily write code to do update in chunks and u can set it up as job as well.

Thanks

Sreejith

ishaan99 SSCertifiable Points: 6261 More actions · Answer 4

Unfortunately my tables doesnt have an identity col so i trying to set up rowcount but that doesnt seem to work. Anythoughts or ideas.

Thanks

Sreejith Sreedharan SSCertifiable Points: 5403 More actions · Answer 5

Can you export the primary keys into a Temp table that has identity. Once that table is populated you can use that as lookup table and update data in chunks.

Thanks

Sreejith

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 6

...and, still, if you have FULL RECOVERY set, none of that will help the LOG file because all of the updates will be recorded whether they be single updates, batches, or 1 big update. The only thing you'll really gain on is NOT locking the whole table during the updates.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 7

Yes, but if you create a job to truncate the log every minute (for example), or to truncate it when it gets to 60% full, then at least you have a fighting chance of the log being truncated at least once before all the updates are completed.

John

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 8

Better make sure you do a backup before you truncate the log or you won't be able to recover to a point in time which is the purpose of Full Recovery....

Personally, I wouldn't worry about the log... let it do it's job and grow. When you're all done, do a backup and then shrink the log file if you find it necessary.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sergiy SSC Guru Points: 110208 More actions · Answer 9

I believe your question was answered here:

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=286040#bm286129

Even name of topics are quite similar.

_____________
Code for TallyGenerator

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 10

Jeff

When I used the word "truncate", I was assuming that the original poster will make the correct choice as to whether to back up the log, or just truncate it. Sometimes the transaction log is on a disk that isn't big enough to be allowed to grow too much.

John

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 11

John,

Understood... I just don't make those same assumptions... if the requester knew about those types of things, they might not have needed to ask the original question

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)