October 23, 2009 at 6:02 am
I have this:
BEGIN
SET ROWCOUNT 4000000;
BEGIN TRANSACTION
Update table1
SET table1.Qty = table2.Qty
from table2
commit
END
How can I write code when this updates 4000000, do it again and then again?Thank you
October 23, 2009 at 6:06 am
How are you stopping the loop updating the same rows as the previous iteration ?
October 23, 2009 at 6:15 am
Forget the loop, that update's wrong.
Unless there's only one for in Table2, there is no way that SQL can tell which row in table2 it should use to update which row in table1. You've got a mess here, not an update statement.
Can you give us your requirements? What exactly it is that you're trying to do here?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 23, 2009 at 7:27 am
I am trying to update 48 million records and I don't want to update them at the same time. What I am doing worong?
BEGIN
SET ROWCOUNT 4000000;
BEGIN TRANSACTION
Update table1
SET table1.Qty = table2.Qty
FROM table1 INNER JOIN
table2 ON table1.KU = table2.KU
WHERE (table1.Qty IS NULL)
COMMIT
END
October 23, 2009 at 9:20 pm
No need to declare an explicit transaction unless you're server is setup to require it. The default is not to require it.
Here's how I do such things in SQL Server 2000...
SET ROWCOUNT 4000000;
WHILE 1 = 1
BEGIN
UPDATE t1
SET Qty = t2.Qty
FROM dbo.Table1 t1
INNER JOIN dbo.Table2 t2 ON t1.KU = t2.KU
WHERE t1.Qty IS NULL;
IF @@ROWCOUNT < 4000000 BREAK;
END;
Best to make sure that KU is a PK... preferably a clustered one.
My recommendation would be to drop the SET and BREAK amounts to just a million and add a WAIT FOR DELAY '00:00:30' to let other processes in... You may also want to add a TABLOCKX table hint just to make sure that no one manages a lock on the rows as they escalate to page and extent locks. It may well lock the entire table anyway so it could save a lot of escalation time.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 26, 2009 at 6:31 am
SET ROWCOUNT 4000000;
WHILE 1 = 1
BEGIN
UPDATE t1
SET Qty = t2.Qty
FROM dbo.Table1 t1
INNER JOIN dbo.Table2 t2 ON t1.KU = t2.KU
WHERE t1.Qty IS NULL;
IF @@ROWCOUNT < 4000000 BREAK;
END;
The way I understand the loop is running until hits < 4 million of records, why are you not using Begin Transaction and just begin, and where would I put Tablockx. Is Tablockx I would use so no one wouldn't lock the table?Is it slowing the speed of my process? How would I write in this code wait for some time and then continue? Thank you
October 26, 2009 at 6:38 am
Krasavita (10/26/2009)
The way I understand the loop is running until hits < 4 million of records
Yeah, pretty much. Since rowcount is 4 million, the only time the update will do less than that is when there's less than 4 million needing updating. Which means the loop is done.
, why are you not using Begin Transaction and just begin,
Because if you use Begin Transaction you're forcing all of the updates within a single transaction. The log's going to grow massively. Do all of the updates absolutely have to be done within a transaction (as a single operation, able to roll the whole lot back)?
How would I write in this code wait for some time and then continue? Thank you
Look up WAITFOR DELAY in Books Online. Question is, why would you want to pause between updates?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 26, 2009 at 7:09 am
Thank you, I don't want to pause anytime,just wanted to know for future if I need too. So, I have 48 mil to update in production, If I write this code with loop, during it is running every 4 mil, what should I write in code and where how many records were updated?
October 26, 2009 at 10:06 am
Krasavita (10/26/2009)
Thank you, I don't want to pause anytime,just wanted to know for future if I need too. So, I have 48 mil to update in production, If I write this code with loop, during it is running every 4 mil, what should I write in code and where how many records were updated?
Ummmm.... you may want to let is pause so that if anything else needs the table for any reason, it has a chance to get a word in edgewise....
--Jeff Moden
Change is inevitable... Change for the better is not.
October 26, 2009 at 1:22 pm
SET ROWCOUNT 4000000;
WHILE 1 = 1
BEGIN
UPDATE t1
SET Qty = t2.Qty
FROM dbo.Table1 t1
INNER JOIN dbo.Table2 t2 ON t1.KU = t2.KU
WHERE t1.Qty IS NULL;
IF @@ROWCOUNT < 4000000 BREAK;
END;
Why are you not using commit, I want to commit every time it hits 4 million rows, where do I put it?
October 26, 2009 at 1:44 pm
Krasavita (10/26/2009)
SET ROWCOUNT 4000000;WHILE 1 = 1
BEGIN
UPDATE t1
SET Qty = t2.Qty
FROM dbo.Table1 t1
INNER JOIN dbo.Table2 t2 ON t1.KU = t2.KU
WHERE t1.Qty IS NULL;
IF @@ROWCOUNT < 4000000 BREAK;
END;
Why are you not using commit, I want to commit every time it hits 4 million rows, where do I put it?
There is no commit as this is using an IMPLICIT transaction. With this code, an IMPLICIT BEGIN TRANSACTION occurs and when completed an IMPLICIT COMMIT TRANSACTION occurs.
Another concern is your transaction log. If your database is using the BULK-LOGGED or FULL recovery model, you may also want to include a BACKUP LOG in the while loop to help keep the transaction log file from growing excessively.
October 26, 2009 at 1:48 pm
I would write the code more like this:
DECLARE @RowsUpdated int;
SET ROWCOUNT 4000000;
WHILE 1 = 1
BEGIN
UPDATE t1
SET Qty = t2.Qty
FROM dbo.Table1 t1
INNER JOIN dbo.Table2 t2 ON t1.KU = t2.KU
WHERE t1.Qty IS NULL;
SET @RowsUpdated = @@ROWCOUNT;
BACKUP LOG ... -- BACKUP LOG to manage transaction log file growth
IF @RowsUpdated < 4000000 BREAK;
END;
SET ROWCOUNT 0;
October 26, 2009 at 2:35 pm
DECLARE @RowsUpdated int;
SET ROWCOUNT 4000000;
WHILE 1 = 1
BEGIN
UPDATE t1
SET Qty = t2.Qty
FROM dbo.Table1 t1
INNER JOIN dbo.Table2 t2 ON t1.KU = t2.KU
WHERE t1.Qty IS NULL;
SET @RowsUpdated = @@ROWCOUNT;
BACKUP LOG ... -- BACKUP LOG to manage transaction log file growth
IF @RowsUpdated < 4000000 BREAK;
END;
SET ROWCOUNT 0;
I am trying to run code with out begin Tran and it takes long time, why? How long you think would take to update without traffic 48 million records and whst set rowcount o means. Yes my db is Full
October 26, 2009 at 3:47 pm
First, look up SET ROWCOUNT in BOL (Books Online, the SQL Server Help System), it will explain everything you need to know about SET ROWCOUNT that would want to know but were afraid to ask.
Second, your batch size may be too large, you may want to reduce the size of the batches you are attempting to process at one time, perhaps SET ROWCOUNT 1000000.
October 26, 2009 at 8:25 pm
Thank you, but this code I run takes to long and Begin Tran takes much faster,do you know why?
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply