question

wish.mannat avatar image
wish.mannat asked

Updating the records of a table simultaneously

I need to update the data of a table simultaneously from four different SSMSs. I have 100 million data, with one PK. I update different rows in order to avoid locking i.e. 1 to 25 million in first SSMS, 25000001 to 50 million in next and so on. CREATE TABLE [dbo].[100Million] ( [Int1] [bigint] NOT NULL PRIMARY KEY, [Int2] [bigint] NULL, [VARCHAR1] [varchar](max) NULL, [VARCHAR2] [varchar](max) NULL ) I used a loop to update 5000 records at a time in order to avoid row locks being escalated to table locks. DECLARE @beginRows INTEGER, @loop INTEGER SET @beginRows = 25000001 SET @loop = 5000 WHILE(@loop >= 1) BEGIN UPDATE [100Million] SET Int2 = 2222 WHERE Int1 BETWEEN @beginRows AND @beginRows + 5000 SET @beginRows = @beginRows + 5001 SET @loop = @loop - 1 END The execution time is not clearly indicating that whether the data is updated in parallel or sequentially. My question are: 1. Can we update data like this in parallel to fasten our process? 2. If not, is there any other method to do so as I have 500 million records in my table.
sql-server-2012updatelocking
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers

· Write an Answer

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.