Updating Millions of records Efficiently | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Updating Millions of records Efficiently

I have two tables in MS SQL SERVER, as following 1. Table1 with 1000 Records with three fields a, b and c 2. Table2 with 13+ Million Records with three fields x, a, b, c, d, e and f Now i want to update the fields Table2.b and Table2.c with Table1.b and Table1.c respectively by field "a" of Table1 and Table2. Currently if i am using a join condition in update statement as given below:
"update Table2 set b = Table1.b, c = Table1.c from Table1, Table2
where Table1.a = Table2.a" This update is taking many hours to do update. Can u please suggest if i have any other options to update the table much faster than this? Thanks in Advance.
Ashish.
Ashish Patel
(Programmer)
Vadodara – India
Kindly don’t duplicate posts. Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
Officially, you should be using the JOIN syntax: update Table2 set b = Table1.b, c = Table1.c
from Table1 INNER JOIN Table2
ON Table1.a = Table2.a Not sure if it will improve the performance. What kind of indexes do you have on these two tables? Sounds as if one or two may be missing.
Brad, Satya please lock this one. Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
Follow as suggested and continue further discussions athttp://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=5726 thread.
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
]]>