Click here to Skip to main content
14,937,005 members
Articles / Web Development / ASP.NET
Article
Posted 24 Jan 2006

Stats

88.8K views
31 bookmarked

Batch Update on a very huge table

Rate me:
Please Sign up or sign in to vote.
3.12/5 (7 votes)
24 Jan 20062 min read
Updating a huge table in Production , without locking the whole table .

Those of us who work in the Production / Live DB , know the pain , of firing a update query on a huge table . This is very problematic as this will lead to the following

  • Lock escalation : The entire table will be locked by your application , so other users /  application will not be able to perform the DDL on this table
  • The TempDB will grow huge
  • A update lock on a table will stop other users , for making changes in the table

I have written a below snippet which will help us fight the above issues

-- Step 1 : Declare the varaibles

use DBNAME<BR>Declare @counter int <BR>Declare @RowsEffected int <BR>Declare @RowsCnt int <BR>Declare @CodeId int <BR>Declare @Err int<BR>SELECT @COUNTER = 1<BR>SELECT @RowsEffected = 0
 


/*
Step 2 : Get the value of the Code , with which we need to update the existing Code . In my case I am capturing is from a table , we can always hard code it .
*/


SELECT @CodeID = CodeID FROM CODE WHERE XXXX ='YYYY'


/*
Step 3: Start the while loop ,  if we have 100,000 records , and in each loop 5,000 records will be update , so the total number of cycle will be 100,000/5000 i.e 20
*/

WHILE ( @COUNTER > 0)<BR> BEGIN <BR> SET ROWCOUNT 5000


-- Note : The SET ROWCOUNT 5000   will just pick the top 5000 records */

   
      /*UPDATING TABLE */
  UPDATE Table<BR> SET CodeID= @CodeID <BR> WHERE Codeid = @OldCode <BR> SELECT @RowsCnt = @@ROWCOUNT ,@Err = @@error <BR> IF @Err <> 0 <BR> BEGIN <BR> Print 'Problem Updating the records' <BR> END <BR> IF @RowsCnt = 0<BR> SELECT @COUNTER = 0 <BR> ELSE <BR>
/* Increment the Counter */

  SELECT @RowsEffected = @RowsEffected + @RowsCnt <BR> PRINT 'The total number of rows effected :'+convert(varchar,@RowsEffected) <BR>
            /*delaying the Loop for 10 secs , so that Update is comepleted*/    

     WAITFOR DELAY '00:00:10'         
 END


--Step 4 : Check if all the records are updated or not .
IF EXISTS ( SELECT CodeID<BR> FROM Table (NOLOCK)<BR> WHERE CodeID = @OldCodeid<BR> )<BR>BEGIN <BR>PRINT ('All the records are not updated , there is some problem , Contact Devs ')<BR>END<BR>BEGIN <BR>PRINT ('All the records are updated SUCCESSFULLY !!!!')<BR>END <BR>/* ------Set rowcount to default ----*/
SET ROWCOUNT 0

 

 

 

 

 

 


 

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Himanshu Kumar Sinha
Web Developer
United States United States
He is working with reputed IT company in Hyderabad India .

He has got an total IT expereince of 4 yrs . I have been working with VB , .NET & SQL SERVER 2000 & Yukon .

he has done his B.Tech In Computer Science & Engg from North Eastern Regional Institute Of Science & Technolgy ( NERIST ) Arunachal Pradesh , Indian .

He enjoy's working on the latest technology , driving & cooking .


Comments and Discussions

 
GeneralMy vote of 1 Pin
Henry.Scott16-Jul-12 9:23
MemberHenry.Scott16-Jul-12 9:23 
GeneralDynamic Parallelization without Coding Pin
Member 79040187-May-11 1:13
MemberMember 79040187-May-11 1:13 
GeneralClumsy Implementation Pin
ncarey1-Feb-06 10:26
Memberncarey1-Feb-06 10:26 
GeneralSuggestion: Before & After Pin
Drew Noakes30-Jan-06 22:14
MemberDrew Noakes30-Jan-06 22:14 
GeneralErrors Pin
Rolenun30-Jan-06 6:01
MemberRolenun30-Jan-06 6:01 
GeneralMore Details Pin
John Kendrick24-Jan-06 7:11
MemberJohn Kendrick24-Jan-06 7:11 
GeneralRe: More Details Pin
totig24-Jan-06 22:34
Membertotig24-Jan-06 22:34 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.