SQL Server Performance Forum – Threads Archive
Update on huge volumes takes long time
I have to update a table tableA(20M records) joining with another table tableB(10 million records) About 8 million records in tableB satisfy the join condition.The remaining 2 million records in tableB have null value in joining column(c1).
Both tables have around 35 columns The query is taking about 20 hours to complete.
Server is healthy (4gb,4cpus,san..etc)
typical query looks like update A
set a.c2=b.c2
a.c3=b.c3
…..
a.c35=b.c35
from tableA A
inner join tableB B
on A.c1=b.C1 Is there any way to run this query faster?
( instead of update, join two tables and insert into a third table)
advance thanks for your help regards
ramasubbup
Disable foreign keys and update —————————————-
Cast your vote
http://www.geocities.com/dineshasanka/sqlserver05.html http://spaces.msn.com/members/dineshasanka
Both the tables don’t have any foreign keys and indexes
Did you use the index for the keycolumns?
Post the table structures Madhivanan Failing to plan is Planning to fail
You would definitely want indexes on the fields you are going to JOIN the tables on, or you should expect the update query to run for 20 hours.
You sure you’re updating only the records that need to be? Ie. if you updated half the records a couple of hrs ago, or yesterday even, are you sure they need to be updated again today? 35 columns is a lot to check against, else I’d say to add a where clause specifying where the target column <> to the source column. I just rewrote a 14 hr update, aside from it being in a loop (*cringe*), it was updating rows that didn’t need to be. Once out of the loop…<pre id="code"><font face="courier" size="2" id="code"><br />– table1.bitCol bit not null default(0)<br />update table1<br />set table1.bitCol = 1<br />from table1<br />join table2 on…<br />where not exists<br /> (…)</font id="code"></pre id="code"><br />… took around 11 minutes to run (again, this is why we avoid loops, right<img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ /> ), but was sadly still updating millions of rows where table1.bitCol was already = 1. The rewrite…<pre id="code"><font face="courier" size="2" id="code"><br />update table1<br />set table1.bitCol = 1<br />from table1<br />join table2 on…<br /> and table1.bitCol = 0<br />where not exists<br /> (…)</font id="code"></pre id="code"><br />…exec’d in around 2.5 – 3 minutes. Both tables are in the 4 – 10 million row range.
]]>