update

Question

update

Tara-1044200

SSCoach

Points: 15951
More actions
October 18, 2010 at 9:49 am

#227984

I have to update column in a table(60million records) from a table of anohte server, records to be updated are nearly 3 million. looking for the best wat to do.
column to be updated is ServerA.RevProd.EmpNo
Have to join to this table ServerB.BudProd.EmpNo
I have to check if any EmpNo on ServerB has different value when compared to the EmpNo of ServerA, if found any i have to update the value from ServerB on ServerA.

Viewing 6 posts - 1 through 5 (of 5 total)

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

I would import the minimum possible amount of data (only required fields) from the remote server into a temp table, index it properly, and then update the local table.

Such a huge update will probably end up locking the whole table, so I would also use WITH TABLOCK.

-- Gianluca Sartori

GSquared SSC Guru Points: 260824 More actions · Answer 2

If it's a large amount of data from Server B, then you might want to insert into a table variable, then from that insert into a temp table that you can index. The table variable will avoid the distributed transaction coordinator and the transactions that creates on both servers. It's a sneaky way to increase speed when dealing with that kind of thing.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

GSquared (10/18/2010)
If it's a large amount of data from Server B, then you might want to insert into a table variable, then from that insert into a temp table that you can index. The table variable will avoid the distributed transaction coordinator and the transactions that creates on both servers. It's a sneaky way to increase speed when dealing with that kind of thing.

Nice tip, Gus!

Thanks for sharing your "dirty tricks"!

-- Gianluca Sartori

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

Here is the logic i am looking for.

ServerA.RevProd (Column1,Column2,Column3,Column4,EmpNo) - Column1-4 are composite key

ServerB.BudProd (EmpNo,EmpNo_Old)

Here EmpNo on serverA is same as EmpNo_Old on ServerB so by mapping them i have to update ServerA.RevProd.Empno on ServerA to the value of ServerB.BudProd.EmpNo where ServerA.RevProd.Empno=ServerB.BudProd.EmpNo_old.

So do i have to create another dummy column like ServerA.RevProd.Empno_temp to be on safe side so that later i can swith the column names.

GSquared SSC Guru Points: 260824 More actions · Answer 5

I wouldn't bother with a temp column of that sort. If you want to keep the old values, create a table specifically for that.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon