x

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.

more ▼

asked Mar 20 at 04:14 AM in Default

avatar image

wish.mannat
52 1 1 5

(comments are locked)
10|1200 characters needed characters left

0 answers: sort voted first
Be the first one to answer this question
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x397
x174
x50

asked: Mar 20 at 04:14 AM

Seen: 51 times

Last Updated: Mar 20 at 12:28 PM

Copyright 2017 Redgate Software. Privacy Policy