March 25, 2009 at 1:04 pm
I need to do an update on a big table, and wanted to update a reasonable number of records at a time.
And since set rowcount was recommened not to be used anymore by MS, top is to be used.
Does that mean one would have to put it through a while loop? and some how get the counts of
all the records of the table and loop it through? but if so, the top is not needed, because you can
just use the count to set the increment, right? I just need someone to point me into the
right direction of how to do what I need properly. so, any input would be appreciated.
the sample table is below, and becuase it is too small here, so would be top 2 at at time for update.
in reality, it may be from 500,000 to 1,000,000 records at a time for update
I need to update all of them to value 10 for salecount for instance.
so, woud using top be a good idea?
create table table1
(
compid int identity(1,1),
salecount int
)
select 10
union
select 20
union
select 55
union
select 66
union
select 15
union
select 10
union
select 10
union
select 20
union
select 55
union
select 66
union
select 15
union
select 10
March 25, 2009 at 1:14 pm
DDL of the tables, some sample data and some sample of the output you expect will help us help you.
* Noel
March 25, 2009 at 1:19 pm
Hi
It is advisable to handle really huge updates in batches. The TOP function helps you to handle this. Here a little sample:
DECLARE @big TABLE (id INT IDENTITY, value1 INT, to_update VARCHAR(100))
INSERT INTO @big
SELECT TOP(1000) sc1.column_id, NULL
FROM master.sys.columns sc1, master.sys.columns sc2
DECLARE @batch INT
DECLARE @all INT
DECLARE @current INT
SELECT @batch = 100, @all = 0, @current = 0
WHILE (1 = 1)
BEGIN
UPDATE TOP(@batch) @big SET to_update = 'updated' WHERE to_update IS NULL
SELECT @current = @@ROWCOUNT, @all = @all + @@ROWCOUNT
IF (@current = 0)
BREAK
END
PRINT 'Did: ' + CONVERT(VARCHAR(10), @all)
Greets
Flo
How to Post Data/Code to get the best Help How to Post Performance Problems
March 25, 2009 at 1:35 pm
Thanks a lot Flo for responding. I'm sorry I ask this because I'm not familiar update in batches, but where would my update statement of the table I need go in your statement?
March 25, 2009 at 1:47 pm
Hi sqlblue
I just added comments to my sample script. Now you should see the place for the UPDATE statement:
DECLARE @big TABLE (id INT IDENTITY, value1 INT, to_update VARCHAR(100))
-- "Many" sample data
INSERT INTO @big
SELECT TOP(1000) sc1.column_id, NULL
FROM master.sys.columns sc1, master.sys.columns sc2
DECLARE @batch INT
DECLARE @all INT
DECLARE @current INT
-- @batch specifies the wanted count of rows to be handled in one step
SELECT @batch = 100,
-- @all remembers the complete count of updated rows
@all = 0,
-- @current remembers the current count of affected rows
@current = 0
-- Infinite loop
WHILE (1 = 1)
BEGIN
-- ###############################
--
-- || THE UPDATE STATEMENT
-- \||/
-- \/
UPDATE TOP(@batch) @big SET to_update = 'updated' WHERE to_update IS NULL
-- / -- /|| -- || THE UPDATE STATEMENT
-- Get the current count of affected items and increase the cmplete count
SELECT @current = @@ROWCOUNT, @all = @all + @@ROWCOUNT
-- If currently we had no items BREAK the loop
IF (@current = 0)
BREAK
END
-- Sample output
PRINT 'Did: ' + CONVERT(VARCHAR(10), @all)
:hehe:
Greets
Flo
How to Post Data/Code to get the best Help How to Post Performance Problems
March 25, 2009 at 1:56 pm
Lol, good job Flo, I love how you pointed that out, 😎
Cheers,
J-F
March 25, 2009 at 2:03 pm
Thanks very much Flo. I really appreciate you take the time
and help me out.
March 25, 2009 at 2:06 pm
Flo, Thanks very much for this post. Very helpful to me as well.
March 25, 2009 at 2:07 pm
You're always welcome!
... sorry for the little joke. 😉
Greets
Flo
How to Post Data/Code to get the best Help How to Post Performance Problems
March 25, 2009 at 2:24 pm
[font="Verdana"]I concur with Flo: this has been my approach to batched updates (or inserts, or deletes) in the past.
It was a bit harder in SQL Server 2000: you had to encode the update as dynamic SQL so you could support a variable batch size. Much easier in 2005. 😀
[/font]
March 25, 2009 at 2:25 pm
No problem. I have never seen a sample generated that way before, it's pretty smart of you. So, that is why I got confused of the update statement for the batch. I guess I got a lot to learn about t-sql. The more I learn the more I realize I don't know anything. Oh well, will have to keep trying.
March 25, 2009 at 2:55 pm
Bruce W Cassidy (3/25/2009)
[font="Verdana"]I concur with Flo: this has been my approach to batched updates (or inserts, or deletes) in the past.It was a bit harder in SQL Server 2000: you had to encode the update as dynamic SQL so you could support a variable batch size. Much easier in 2005. 😀
[/font]
Hi Bruce
I don't understand. Why the dynamic SQL? I'm still working with some SQL Server 2000. With ROWCOUNT you can do the same:
-- ...
SET ROWCOUNT @batch
UPDATE @big SET to_update = 'updated' WHERE to_update IS NULL
SELECT @current = @@ROWCOUNT, @all = @all + @@ROWCOUNT
SET ROWCOUNT 0
-- ...
It is less handy than the TOP but it does the same thing.
Greets
Flo
How to Post Data/Code to get the best Help How to Post Performance Problems
March 25, 2009 at 3:09 pm
Florian Reischl (3/25/2009)
Why the dynamic SQL? I'm still working with some SQL Server 2000. With ROWCOUNT you can do the same.
[font="Verdana"]Yes, but the performance is far worse than using TOP. And usually when you are working with large batches, you want good performance. So the dynamic SQL works faster.[/font]
March 25, 2009 at 3:12 pm
I see! I didn't know.
Thanks for assertion (again 🙂 )!
How to Post Data/Code to get the best Help How to Post Performance Problems
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply