March 27, 2008 at 11:47 am
Hi Guys,
I have a huge table and I need to run an update script that updates around 300 columns and 100 million records. Does anyone have example of a code that we can do update in batches?
Thanks for help.
March 27, 2008 at 1:22 pm
March 27, 2008 at 1:30 pm
Something to get you started:
CREATE Procedure spHugeTable_IncrementalUpdate( @RowsPerBatch int )
AS
Update HugeTable
Top (@RowsPerBatch)
Set {columns to change, ...}
, UpdateCol = 1
Where UpdateCol=0
Now write a SQL Job that just calls this procedure and schedule it to run every X minutes.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 27, 2008 at 1:45 pm
Top rows is not good for 2000.
March 27, 2008 at 10:11 pm
Ghanta (3/27/2008)
Top rows is not good for 2000.
that is true, but your will note that the title of the forum group is "SQL Server 2005". There is a different forum group for SQL Server 7 & 2000.
If you are on SQL 2000, then SET ROWCOUNT will also work, though it is deprecated in SQL 2005.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 28, 2008 at 3:22 am
Check out this post..
http://www.sqlservercentral.com/Forums/Topic474485-146-1.aspx?Update=1
"Keep Trying"
March 28, 2008 at 7:20 am
rbarryyoung (3/27/2008)
Something to get you started:
CREATE Procedure spHugeTable_IncrementalUpdate( @RowsPerBatch int )
AS
Update HugeTable
Top (@RowsPerBatch)
Set {columns to change, ...}
, UpdateCol = 1
Where UpdateCol=0
Now write a SQL Job that just calls this procedure and schedule it to run every X minutes.
rbarryyoung
I have the table with over 1 500 000 records and I want to update this table just in one column and I will write the original code what I'm trying to update so can you help me how to integrate in your SP here
Update code is:
Update GHOSTDATA
SET Regjimi = 'MI88'
WHERE NR_KEK LIKE '08%' OR NR_KEK LIKE '07%' OR NR_KEK LIKE '06%';
I try to update as I write it up but it takes over 30 min to update! Is it any possibility with less time!
thnx!
Dugi
March 28, 2008 at 7:43 am
It all depends on several factors but 30 minutes may be the best you can do.
On thing to keep in mind is how the change is handled in the transaction log. The row is written as is to the transaction log as a delete and then the row in it's changed state is written to the transaction log as an insert then the commit is fired at some point during the batch if a rollback does not occurr. So depending on your server hardware and file layout plus the byte size of the rows this can slow things, plus transaction log file growth will add to this (this is where batching in groups of a few thousand can be helpfull).
Additionally other transaction can lock rows you are trying to update and delay you. Plus there are considerations for what indexes are there to use and what changes happen to any indexes becuase of the change to the data.
As to how you can use I would look at doing as stated but this way
CREATE PROC BigBatch
AS
SET NOCOUNT ON
SET ROWCOUNT 25000
UPDATE
GHOSTDATA
SET
Regjimi = 'MI88'
WHERE
LEFT(NR_KEK,2) IN ('08','07','06') AND
Regjimi != 'MI88'
GO
Note the LEFT(NR_KEK,2) IN ('08','07','06') replaces your likes just to simplfy visually but may want to keep your LIKE's. I added the line Regjimi != 'MI88' so you can run the batch multiple times (affecting only 25000 records at a go) and not update recorss you already changed.
Can't guarantee < 30 minutes but can be a little less stressfull to the server (especially with regards to the TL file). I would suggest if you don't already have an index on NR_KEK and Regjimi that adding an index to both (composite best in this case, but two seperate can help as well) you might find it will help. However keep in mind that creating it may take longer than 30 mintues total to create the index(es) plus run the batch so you may not buy anything.
Hope that all helps.
March 28, 2008 at 7:48 am
Dugi (3/28/2008)
I have the table with over 1 500 000 records and I want to update this table just in one column and I will write the original code what I'm trying to update so can you help me how to integrate in your SP hereUpdate code is:
Update GHOSTDATA
SET Regjimi = 'MI88'
WHERE NR_KEK LIKE '08%' OR NR_KEK LIKE '07%' OR NR_KEK LIKE '06%';
Try this:
CREATE Procedure spGHOSTDATA_IncrementalUpdate( @RowsPerBatch int )
AS
Update GHOSTDATA
Top (@RowsPerBatch)
Set Regjimi = 'MI88'
Where Regjimi <> 'MI88'
And (NR_KEK LIKE '08%' OR NR_KEK LIKE '07%' OR NR_KEK LIKE '06%')
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 28, 2008 at 7:51 am
Antares686:
Thnx for fast reply but this is very simple what you wrote here, ok suggestion for the Left(NR_KEK,2) ... thank you so much ...
but the SP is doesn't look like the above with TOP(@RowsPerBatch)) etc etc...
thnx anyway !
Dugi
March 28, 2008 at 7:58 am
rbarryyoung (3/28/2008)
Dugi (3/28/2008)
I have the table with over 1 500 000 records and I want to update this table just in one column and I will write the original code what I'm trying to update so can you help me how to integrate in your SP hereUpdate code is:
Update GHOSTDATA
SET Regjimi = 'MI88'
WHERE NR_KEK LIKE '08%' OR NR_KEK LIKE '07%' OR NR_KEK LIKE '06%';
Try this:
CREATE Procedure spGHOSTDATA_IncrementalUpdate( @RowsPerBatch int )AS
Update GHOSTDATA
Top (@RowsPerBatch)
Set Regjimi = 'MI88'
Where Regjimi <> 'MI88'
And (NR_KEK LIKE '08%' OR NR_KEK LIKE '07%' OR NR_KEK LIKE '06%')
OK I'm trying but still have problem with these message:
...
Incorrect syntax near the keyword 'Top'.
so this is what I'm asking for ...why this incorrect msg!!!??!?!?
thnx
Dugi
March 28, 2008 at 8:00 am
This would be that SP with the information put in based on what I gave.
CREATE Procedure spHugeTable_IncrementalUpdate( @RowsPerBatch int )
AS
UPDATE
GHOSTDATA
TOP
(@RowsPerBatch)
SET
Regjimi = 'MI88'
WHERE
LEFT(NR_KEK,2) IN ('08','07','06') AND
Regjimi != 'MI88'
GO
So then to update 25000 at a time you would create a job that runs every few minutes with a step like so (or you can just execute by hand after each run).
EXEC spHugeTable_IncrementalUpdate 25000
I usually use SET NOCOUNT ON to reduce messages but you might want to know when 0 records are affected.
March 28, 2008 at 8:05 am
:crying:
Still same problem:
Msg 156, Level 15, State 1, Procedure spHugeTable_IncrementalUpdate, Line 6
Incorrect syntax near the keyword 'TOP'.
March 28, 2008 at 8:15 am
Was curious about that, didn't think top was correct in the syntax. Try this
CREATE Procedure spHugeTable_IncrementalUpdate( @RowsPerBatch int )
AS
SET ROWCOUNT @RowsPerBatch
UPDATE
GHOSTDATA
SET
Regjimi = 'MI88'
WHERE
LEFT(NR_KEK,2) IN ('08','07','06') AND
Regjimi != 'MI88'
GO
March 28, 2008 at 8:19 am
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply