SQL Engine - BULK UPDATE command - SimonS Blog on SQL Server Stuff

SQL Engine - BULK UPDATE command

You can achieve very high performance with the BULK INSERT interfaces in SQL Server 2005, from SSIS, ADO, TSQL or plain old bcp.

Many times you would like to be able to perform bulk updates with the same performance, however there is no command to support this.

If you would like such a feature in a future version of SQL Server then you need to vote on the suggestion at connect

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=212571

If you want proof connect works look at http://sqlblogcasts.com/blogs/simons/archive/2006/09/24/object_name___to_be_extended_in_SP2.aspx


-
Published 04 October 2006 20:43 by simonsabin
Filed under: , ,

Comments

07 November 2007 06:35 by Amar Karandikar

# re: SQL Engine - BULK UPDATE command

Please provide a bulk update feature as the application I'm working on is a data intensive application, and requires common property updates across set of records.

This feature will greatly help us in building data intensive applications.

08 November 2007 01:18 by Peter

# re: SQL Engine - BULK UPDATE command

update ads set adstatus = 100

Updates on 60000 records took over 20 minutes when I stopped it as my laptp was coming to halt.

Code below 32 seconds ...  

DECLARE @ID int

 DECLARE @MyCursor CURSOR

SET @MyCursor = CURSOR FAST_FORWARD

FOR

select id from ads

OPEN @MyCursor

FETCH NEXT FROM @MyCursor

INTO @ID

WHILE @@FETCH_STATUS = 0

BEGIN

--do the insert in here

PRINT @ID

update ads set adstatus = 100 where id = @id

FETCH NEXT FROM @MyCursor

INTO @ID

END

CLOSE @MyCursor

DEALLOCATE @MyCursor

08 November 2007 08:33 by simonsabin

# re: SQL Engine - BULK UPDATE command

I would suspect that is because the first is being done in one transaction where as the second is being done in individual transactions.

Doing it in one transaction requires your log file to be big enough to contain the full update transaction.

Also doing in one transaction may result in contention (blocking) which then causes the process to take longer and then cause more blocking and so it gets worse and worse.