multiple updates

Question

Post reply

multiple updates

Tara-1044200

SSCoach

Points: 15951
More actions
August 3, 2010 at 7:14 pm

#222111

How can i do multiple updates in a single cursor with better performance. I know the systax below is wrong but can someone give an idea how to implement this.
declare @dbname as varchar(80)
declare up_cursor CURSOR for select name from master.sys.databases
where name like ('USP_Rev%')
order by name
open up_cursor
Fetch next from up_cursor into @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
update dbo.Rev_History set fixpaid = 1;
update dbo.Bud_History set fixpaid = 1;
update dbo.Adm_History set fixpaid = 1;
update dbo.Rev_History2
set fixpaid = case when rc.dpaid=1 then 0 else 1 end
from dbo.Rev_History2 as ht inner join
OBBEAVER.USPA_check.dbo.RevID rc on rc.revno=ht.revno
go
update dbo.Bud_History2
set fixpaid = case when rc.dpaid=1 then 0 else 1 end
from dbo.Bud_History2 as ht inner join
OBBEAVER.USPA_check.dbo.RevID rc on rc.revno=ht.revno
go
update dbo.Adm_History2
set fixpaid = case when rc.dpaid=1 then 0 else 1 end
from dbo.Adm_History2 as ht inner join
OBBEAVER.USPA_check.dbo.RevID rc on rc.revno=ht.revno
FETCH NEXT FROM up_cursor INTO @dbname
END
CLOSE up_cursor
deallocate up_cursor
GO

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

Do the multiple updates without the cursor.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Tara-1044200 SSCoach Points: 15951 More actions · Answer 2

how about doing this in batches as it involves 50 millions of records and i dont have that much log space.

how can i optimize this query, it taking very long time.

Tara-1044200 SSCoach Points: 15951 More actions · Answer 3

is this update statement correct?

WHILE (1 = 1)
BEGIN
BEGIN TRANSACTION
update TOP (10000) dbo.Adm_History2
set fixpaid = case when rc.dpaid=1 then 0 else 1 end
from dbo.Adm_History2 as ht inner join
OBBEAVER.USPA_check.dbo.RevID rc on rc.revno=ht.revno
IF @@ROWCOUNT = 0
BEGIN
COMMIT TRANSACTION
BREAK
END
COMMIT TRANSACTION
END

WayneS SSC Guru Points: 95460 More actions · Answer 4

I don't see anything wrong with it.

Another way would be:

declare @rowcount int

set @rowcount = 1

while (@rowcount > 0) begin

begin transaction

update top (10000) ...

set @rowcount = @@rowcount

commit tran

end

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Tara-1044200 SSCoach Points: 15951 More actions · Answer 5

can i do all my updates given above in the same transaction ? I was doubting as it invovles multiple tables.

WayneS SSC Guru Points: 95460 More actions · Answer 6

Sure. Just know that this will cause more transactions in the log file, and since you were concerned about the size of that...

Since you're continually updating the tables until they are finished, what does having all updates in a single transaction do for you? For that matter, what does using a transaction at all do for you?

To update multiple tables in a transaction, your looping logic is going to have to change. What if the first table still has rows to update, and the last one is finished? @@rowcount will be zero, and break out of your loop.

IMO, I would do each table separately in it's own while loop.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Tara-1044200 SSCoach Points: 15951 More actions · Answer 7

thanks for the info.

I have update a table (50 million) witout using batch and it ran for 17hrs and i killed so its rolling back. how do i knoe how long it will take to roll back.

Tara-1044200 SSCoach Points: 15951 More actions · Answer 8

How about creating this set of transaction for each table and adding all these sets into one job step by that way i can do this for multiple databases in a single job.

Tara-1044200 SSCoach Points: 15951 More actions · Answer 9

from the above query, does the update perform better if I create non CI indexes on revno and fixpaid.

scott.pletcher SSCertifiable Points: 5958 More actions · Answer 10

Your rollback will likely take at least roughly twice as long as the original updates.

from dbo.Adm_History2 as ht inner join
OBBEAVER.USPA_check.dbo.RevID rc on rc.revno=ht.revno

Do you have an index on rc.revno?

For this type of UPDATE, you *really* need one, else it will be very slow.

Scott Pletcher, SQL Server MVP 2008-2010