Solved

CHANGING LARGE UPDATE STATEMENT TO RUN IN BATCHES OF 500

Posted on 2008-09-30
27
327 Views
Last Modified: 2010-04-21
I have an update statement that is running on a table. I have given a small example of it below.

The problem I have is that there are 448653 records.

Basically I want to add some sort of loop that will commit the update every 500 records rather than doing them all at the same time.

Can someone give me some advice.

Thanks
UPDATE 

	Customer

SET

	ModifiedDate = GETDATE(),

	ModifiedBy = 808  

FROM 

	Customer c (NOLOCK)

INNER JOIN Prospect p (NOLOCK)

	ON c.CustomerId = p.CustomerId

Open in new window

0
Comment
Question by:scm0sml
  • 11
  • 10
  • 6
27 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 22604582
something like this (if you're on 2005)

declare @rowcount int
set @rowcount = 1

while @rowcount > 0
begin
UPDATE top(500) Customer
SET
        ModifiedDate = GETDATE(),
        ModifiedBy = 808  
FROM
        Customer c (NOLOCK)
INNER JOIN Prospect p (NOLOCK)
        ON c.CustomerId = p.CustomerId

set @rowcount = @@rowcount

end
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 22604588
whoops...forgot something:

something like this (if you're on 2005)

declare @rowcount int
set @rowcount = 1

while @rowcount > 0
begin
UPDATE top(500) Customer
SET
        ModifiedDate = GETDATE(),
        ModifiedBy = 808  
FROM
        Customer c (NOLOCK)
INNER JOIN Prospect p (NOLOCK)
        ON c.CustomerId = p.CustomerId
where modifieddate < getdate() or modifieddate is null

set @rowcount = @@rowcount

end
0
 

Author Comment

by:scm0sml
ID: 22604635
i dont really need to worry about:
where modifieddate < getdate() or modifieddate is null


can you just explain how that loop then knows to do the next 500 etc, so the 2nd time arounf do rows 500-1000 etc?
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 22604671
I included the criteria so you're not constantly updating the same 500 records everytime.

so, it starts the loop and updates 500, and will continue updating in 500 record increments so long as there are records to update.  when you finally run an update statement that does not update any records, the loop will exit.
0
 

Author Comment

by:scm0sml
ID: 22605127
ahhh im with u :)

im getting:
Incorrect syntax near the keyword 'TOP'.

I can't see anything wrong with that can you?
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 22605137
are you on sql 2005?  is your database compatability set to 90?
0
 

Author Comment

by:scm0sml
ID: 22605189
im on 2005 yes.

not sure how to check the compatibility?
0
 

Author Comment

by:scm0sml
ID: 22605200
ITS 80.
0
 

Author Comment

by:scm0sml
ID: 22605209
used sp_dbcmptlevel to find it
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 22605251
you need to change it to 90 if you can.  

use yourdb
exec sp_dbcmptlevel 90
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 22605290
In my opinion this is bad practice. There is no justification to beak an update statement in chunks when it can be done in one batch. This will not necessarily help performance. Overall it will take at least the same amount of time to do the update and maybe more as the query is "prepared" for each chunk. And less then 500 thousand rows are a joke for SQL server.


0
 

Author Comment

by:scm0sml
ID: 22605292
DAMN!!!

ARRGGGHHHHHHHH

I've been an idiot. This is a 2000 database!!

Is it just the top that wont work? is there a work around?
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 22605314
Z - the idea is to not leave one really big transaction open for any large amount of time.  Breaking it into chunks can prevent that
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 26

Expert Comment

by:Zberteoc
ID: 22605323
but for this size of table that statement should not take too long unless you have a lots of indexes on it.
0
 
LVL 60

Accepted Solution

by:
chapmandew earned 500 total points
ID: 22605324
this shoudl work:


declare @rowcount int
set @rowcount = 1

while @rowcount > 0
begin
SET ROWCOUNT 500

UPDATE Customer
SET
        ModifiedDate = GETDATE(),
        ModifiedBy = 808  
FROM
        Customer c (NOLOCK)
INNER JOIN Prospect p (NOLOCK)
        ON c.CustomerId = p.CustomerId
where modifieddate < getdate() or modifieddate is null

set @rowcount = @@rowcount

end
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 22605337
I agree...but your idea of "not that long" and the authors might be different...
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 22605371
on the other hand placing the update in a loop will not make any difference as it will run the update again one iteration after another and the transaction will still be open.
0
 

Author Comment

by:scm0sml
ID: 22605379
There are a lot more joins than i have shown, each of the tables have around half a million records in.

Sorry I'm prob missing something again but can't see what:
SET ROWCOUNT 500

is doing?
0
 

Author Comment

by:scm0sml
ID: 22605386
"on the other hand placing the update in a loop will not make any difference as it will run the update again one iteration after another and the transaction will still be open." - doesnt the transaction commit each time?
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 22605411
they will be different transactions unless you specify a BEGIN TRANSACTION statement.
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 22605418
SET ROWCOUNT is the 2000 version of the UPDATE TOP(500) statement.  I am using it to set the number of records you're updating to 500 each time...make sense.
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 22605429
meant that to say

...make sense?
0
 

Author Comment

by:scm0sml
ID: 22605437
ah right ok im with you.
0
 

Author Closing Comment

by:scm0sml
ID: 31501514
Thanks for your help!!

We got there in the end.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 22605538
OK, try this:
declare

	@upd_date datetime

select 

	@upd_date = GETDATE()
 
 
 

	UPDATE top 500

		Customer

	SET

		ModifiedDate = @upd_date,

		ModifiedBy = 808  

	FROM 

		Customer c (NOLOCK)

		INNER JOIN Prospect p (NOLOCK)

				ON c.CustomerId = p.CustomerId

	where 

		c.ModifiedDate<>@upd_date

-- ### --

while  @@rowcount<>0

begin 

	select 

		@upd_date = GETDATE()
 

	UPDATE top 500

		Customer

	SET

		ModifiedDate = @upd_date,

		ModifiedBy = 808  

	FROM 

		Customer c (NOLOCK)

		INNER JOIN Prospect p (NOLOCK)

				ON c.CustomerId = p.CustomerId

	where 

		c.ModifiedDate<>@upd_date

end 

Open in new window

0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 22605546
oops, won't work
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 22605576
it will run forever. the problem here is that you want to know what rows were already updated and what were not. at the same time you want to update only the rows that were not yet updated. The oly way is to use a timestamp with ModifiedDate column, which I tried but the problem is that it will never stop as already ModifiedDate values will be always <> @upd_date.
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Suggested Solutions

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now