Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Updating an Entire Column in a 10 GB table Expand / Collapse
Author
Message
Posted Monday, December 31, 2012 4:06 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 14, 2014 4:48 AM
Points: 2,834, Visits: 3,950
chandan_jha18 (12/27/2012)
in my update statement, the condition is such that the column may get changed from 1 to 2 and next time when update happens that set of data with value '2' is going to get updated again.
batch approach always give leverage to handle the resources like memory , disk space and also we can schedule it for off-peak hours. and to handle your "adhoc updation hiccups" ,
1) you can set a update trigger so that you can catch the records which are being getting update during batch process
2) and save them in any temp table
3) once the batch got completed pick those values from that temp table and update the new column.
4) drop the temp table and trigger once the new column populated completely.


-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1401352
Posted Monday, December 31, 2012 5:56 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, May 21, 2014 10:49 AM
Points: 21, Visits: 312
Hope this helps

/*Example table to be updated (e.g. your 10GB table) **************/
--DROP TABLE table1
CREATE TABLE table1(tableID INT IDENTITY(1,1) Primary Key, field1 varchar(50),UpdateField varchar(50))
GO
INSERT INTO table1(field1)
SELECT newid()
GO 50
SELECT * FROM table1
/****************************************************/


/******explanation of my post***********/
IF OBJECT_ID('tempdb..#looptemp') IS NOT NULL
BEGIN
DROP TABLE #looptemp
END

CREATE TABLE #looptemp(pkid INT IDENTITY,tableid INT)--tableid will hold the tableid from the above table
INSERT INTO #looptemp(tableid)
SELECT tableid FROM table1

DECLARE @counter INT
DECLARE @tableid INT
SET @counter=1

WHILE @counter <= (SELECT MAX(pkid) FROM #looptemp)
BEGIN
SET @tableid=(SELECT tableid FROM #looptemp WHERE pkid = @counter)


--HERE IS THE UPDATE TO YOUR TABLE
--YOU SPECIFY THE CLUSTERED INDEX ON THE IDENTITY INT
--THIS SHOULD ONLY CAUSE A ROW LOCK
UPDATE table1
set UpdateField=getdate()
WHERE tableID=@tableid

SET @counter=@counter+1
END
/****************************************/
GO
SELECT * FROM table1
Post #1401368
Posted Monday, December 31, 2012 7:13 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 11:29 PM
Points: 459, Visits: 1,889
Thanks NGreene. Will try that very soon and post the results of execution times.

Wish you and your family a very happy new year!!!

Regards
Chandan Jha
Post #1401383
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse