SQL updates on table with over 7 million rows throws out of memory exception

Question

SQL updates on table with over 7 million rows throws out of memory exception

ek-822869

Ten Centuries

Points: 1259
More actions
June 1, 2010 at 5:23 pm

#100258

Hi,
I have written a script which updates one column with zeros in all the rows using whille loop which is working for the tables with small number of rows
select @tot_rows = count(*) from XXX
select @counter = 1
While @counter <= @tot_rows
begin
select @au_var = au from XXX where row_id = @counter
select @account_var = account_nbr from XXX where row_id = @counter
select @entity_var = au_entity from XXX where row_id = @counter
Update XXX set account_nbr = '00'+@account_var where row_id = @counter
Update XXX set au_entity = '00'+@entity_var where row_id = @counter
GO
select @counter = @counter + 1
end
Here row_id is a primary key identity column.
Two problems i have:
1) I changed the recovery model to simple still it is logging to the transaction log and i am getting log full error. As far as i know it shouldn't log anything to transaction log, am i not right ?
2) I increased the transaction log to the maximum but getting out of memory exception, i can't add more memory now but how can i resolve that. I tried using some batches with 200000 rows at a time but it is also taking lot of time. Pls help with any alternatives
This works faster compared to cursor but getting out of memory exception

Viewing 8 posts - 1 through 7 (of 7 total)

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

David Webb-CDS SSCoach Points: 17398 More actions · Answer 1

Simple recovery mode doesn't mean nothing will be logged. The server still needs to log updates so it can back the transaction out if something happens. In simple mode, the checkpoint process will remove log entries for transactions which have been committed, keeping the log smaller, but removing the ability to use a full backup and log backups to roll the database forward from a backup. (There's more to it than that, so a thorough read of BOL would be a good idea).

Why use the loop rather than a structure like

set id = '00' + id from table

which would get them all at once?

And then again, I might be wrong ...
David Webb

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 2

ek-822869 (6/1/2010)
Hi,
I have written a script which updates one column with zeros in all the rows using whille loop which is working for the tables with small number of rows
select @tot_rows = count(*) from XXX
select @counter = 1
While @counter <= @tot_rows
begin
select @au_var = au from XXX where row_id = @counter
select @account_var = account_nbr from XXX where row_id = @counter
select @entity_var = au_entity from XXX where row_id = @counter
Update XXX set account_nbr = '00'+@account_var where row_id = @counter
Update XXX set au_entity = '00'+@entity_var where row_id = @counter
GO
select @counter = @counter + 1
end
Here row_id is a primary key identity column.
Two problems i have:
1) I changed the recovery model to simple still it is logging to the transaction log and i am getting log full error. As far as i know it shouldn't log anything to transaction log, am i not right ?
2) I increased the transaction log to the maximum but getting out of memory exception, i can't add more memory now but how can i resolve that. I tried using some batches with 200000 rows at a time but it is also taking lot of time. Pls help with any alternatives
This works faster compared to cursor but getting out of memory exception

You cannot have the GO in this code because that forms a new batch with no awareness of previous variables. You MUST remove the GO for this to work correctly.

--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)

Bajrang SSCrazy Points: 2074 More actions · Answer 3

Why Can't you just use:

update xxx set account_nbr = '00' + account_nbr

update xxx set au_entity = '00' + au_entity

-RP

Arjun Sivadasan SSCertifiable Points: 5902 More actions · Answer 4

Hey, I cannot really understand why you have to do all these. Try the following query and check if it gives you the desired result.

update XXX set account_nbr = '00' + account_nbr, au_entity = '00' + au_entity

- arjun

https://sqlroadie.com/

Arjun Sivadasan SSCertifiable Points: 5902 More actions · Answer 5

Rups (6/2/2010)
Why Can't you just use:
update xxx set account_nbr = '00' + account_nbr
update xxx set au_entity = '00' + au_entity

I didn't see that Rups had replied. You can do the same in a single update statement as you can see from my post. I also suggest that you take a pen and paper and sit down and think about the problem at hand before you write a query.

- arjun

https://sqlroadie.com/

ek-822869 Ten Centuries Points: 1259 More actions · Answer 6

You are right. the only issue is it updates the empty rows (with blank spaces )with 00 and i have to write another statement to update them to blank. But this is a simple solution. Thank you

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 7

ek-822869 (6/8/2010)
You are right. the only issue is it updates the empty rows (with blank spaces )with 00 and i have to write another statement to update them to blank. But this is a simple solution. Thank you

Just add a WHERE somecolumn > '' and it won't try to update blanks or nulls.

--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)