committing within a loop - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

committing within a loop

Author

Topic

Sean_B
Posting Yak Master

111 Posts

Posted - 2009-08-12 : 16:14:11

Hi I've been running an update on a large table in chunks and committing each chunk as follows.

DECLARE @max INT, @count INT,@err VARCHAR(MAX)

SELECT @max = MAX(vendorcodeid) from vendorcode v

SET @count = 0

WHILE @count < @max
BEGIN
BEGIN transaction
set @COUNT = @count + 1

UPDATE t SET t.vendorcodeid = v.vendorcodeid
FROM vendorcode v
INNER JOIN trade t ON t.equityexchangeid = v.equityexchangeid
AND t.date >= v.datefrom AND t.date < v.dateto
WHERE v.vendorcodeid = @count

IF (@count % 100) = 0
BEGIN
SET @err = CAST(@count AS VARCHAR(10)) + ' of ' + CAST(@max AS VARCHAR(10))
RAISERROR(@err,11,1) WITH nowait
END
commit
END

However this seems not to have committed after each chunk, can you see any reason for this ? I'm wondering if I somehow accidentally left a transaction open before running this as I'm doing it manually?

Sean

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-12 : 16:46:44

I can't break your code as is with my psuedo code. I thought the raiserror in the transaction might be causing a problem but doesn't seem to be. Perhaps you're right that you had an open tran from manual testing. Are you still having the problem? How do you know it isn't committing?

By the way, since you only have one statement in there you don't even need an explicit transaction. The implicit transaction of the update will accomplish the same thing.


create table #t (i int)
DECLARE @max INT, @count INT,@err VARCHAR(MAX)

SELECT @max = 10
SET @count = 0

WHILE @count < @max
BEGIN
       BEGIN transaction
              set @COUNT = @count + 1

              insert #t (i) values (@count)

              IF (@count % 9) = 0
              BEGIN
                     SET @err = CAST(@count AS VARCHAR(10)) + ' of ' + CAST(@max AS VARCHAR(10))
                     RAISERROR(@err,11,1) WITH nowait
              END
       select @@trancount [tran_count_in]
       commit
       select @@trancount [tran_count_after]
END

select * from #t
drop table #t

Be One with the Optimizer
TG

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-12 : 16:46:49

What you're doing there is a loop from zero to max with a begin tran and commit tran for each record.
The only thing in relation to a "chunk" I can see is RAISEERROR.

No, you're never too old to Yak'n'Roll if you're too young to die.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-12 : 17:09:14

quote:
Originally posted by webfred

What you're doing there is a loop from zero to max with a begin tran and commit tran for each record.
The only thing in relation to a "chunk" I can see is RAISEERROR.

No, you're never too old to Yak'n'Roll if you're too young to die.

He's updating based on @count (WHERE v.vendorcodeid = @count) which incremented with each loop.

Be One with the Optimizer
TG

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-12 : 17:11:21

Yes I see.
But I think he wanted a tran for each block of 100 records (chunk).
But his coding isn't like that.

No, you're never too old to Yak'n'Roll if you're too young to die.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-12 : 17:18:00

Oh I see what you mean. The code (and my first impression) is committing after every 1 @count which wouldn't even need a tran block. But you think maybe he wants to commit after every 100 @counts. Then that leads to the question why a seperate update for each 1 rather than a single update for 100 at a time ???

Be One with the Optimizer
TG

Sean_B
Posting Yak Master

111 Posts

Posted - 2009-08-12 : 17:19:44

Hi, I checked the transaction count using the monitor, and I had somehow left some transactions open in the initial part of my testing, I've since run the script and it seems good, I'm using the vendorcodeid as an easy way to chunk up the updates, but I'm only putting out a message very 100 vendorcodes.
Thanks for looking at this and sorry to trouble you.

Sean

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-12 : 17:24:02

No trouble - just killing the last few minutes of the day...

Drop the begin/end tran. You don't need it.

Be One with the Optimizer
TG

Sean_B
Posting Yak Master

111 Posts

Posted - 2009-08-12 : 18:18:09

I got rid of the begin / end tran, what was I thinking.

Sean

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources