Speedup to update table
-
Hi,
I try to update table to asign random number to each row. The table has 4 million rows and each row updated 0.5 second. It will take 555 hours or 23 days to update the table.
Any idea how to speedup the update statement?
Declare @id varchar(64), @SmpID varchar(64)
DECLARE cust_cursor CURSOR FOR SELECT id,SmpID FROM Table_1
OPEN cust_cursor
FETCH NEXT FROM cust_cursor
INTO @id, @SmpID
WHILE @@FETCH_STATUS = 0
BEGIN UPDATE Table_1
SET RND = RAND() * 100000000000000
WHERE id = @id AND SmpID = @SmpID
FETCH NEXT FROM cust_cursor INTO @id, @SmpID
END
CLOSE cust_cursor
DEALLOCATE cust_cursor
Regard,
M.Z.
-
Replace
WHERE id = @id AND SmpID = @SmpID
with:
WHERE
CURRENT OF cust_cursor
In my tests it reduced execution time about 7 times.
-
thanks. It helps.
-
Why even use a cursor for this, you can reinitialize the the rand() for each row and do a single update which would most likely take only a few seconds (assuming you can lock the whole table to do this update, if not you can always do the updates in batch).
-
Like Remi says... why use a
cursor???UPDATE TABLE_1
SET Rnd = RAND(CAST(NEWID() AS VARBINARY)) * 10000000000000
--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) -
But this generates the same random number 4 million times. Maybe that's why he used the cursor.
-
Did you actually run the statement before saying that?? The data will be unique for each row.
-
Yep. And it doesn't work. The selects below always generate just one record with a count of 4000000.
create table temp1(randnum bigint)
insert into temp1
select top 4000000 rand()*10000000000000
from somebigtable
select randnum,count(*)
from temp1
group by randnum
having count(*)>1
update temp1
set randnum=rand()*10000000000000
select randnum,count(*)
from temp1
group by randnum
having count(*)>1
-
To get different values you need to mess with the function newid().
-
You need to have another look there fella... I DID use NEWID() in my code... you didn't in your test.
--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) -
Man are you right. I stopped at RAND and didn't bother to look what it's being passed. Sorry.
-
Why do you think he used THIS CODE???
RND(CAST(NEWID() AS VARBINARY)) * 10000000000000
-
And this is the code that works in 2000 :
SELECT RAND(CAST(NEWID() AS VARBINARY)) * 10000000000000 AS Test FROM master.dbo.SysColumns
-
Indeed! Although the SS2K BOL documents allowable seeds for RAND() as limited to integer types. But if varbinary works, so be it.
-
Heh... no worries... I've done the same thing when I got in a hurry
I even mispelled "RAND" in my first post and had to go back and correct it 
--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)
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply