?
Solved

updating one column on 18 million record table

Posted on 2011-09-23
9
Medium Priority
?
219 Views
Last Modified: 2012-05-12
there is an update on a column and the inner join is between two table. 18M records are being affected.the query is running for 1 hour and log has grown from 1.5 GB to 16 GB.. there is 20 GB more available on the disk.. so it is OK for now..

theh recovery is simple

is it possible to ask it to commit each update and not do it as a transaction, so that the log is not impacted like this?
0
Comment
Question by:25112
9 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 500 total points
ID: 36590678
space seems to be fine
you can try shrinking the logfile from another query window, this may slow down your update

DBCC SHRINKFILE(logName)

>is it possible to ask it to commit each update and not do it as a transaction, so that the log is not impacted like this?
no, you need to do these kinds of perations as small batches. like updating 10K records at a time
0
 
LVL 5

Author Comment

by:25112
ID: 36590829
>>no, you need to do these kinds of perations as small batches. like updating 10K records at a time
10K records - you mean manually? it will be troublesome with 18M records.. how do we track which ones have been updated yet or not etc

the join is as simple as
update a set col1 = b.col1 from tablea a join tableb b on a.id = b.id and a.col2='value' and b.col2='value'
0
 
LVL 42

Assisted Solution

by:dqmq
dqmq earned 1000 total points
ID: 36590874
This will update 10000 rows that havn't been updated yet.  Repeat until no rows are updated.


update a set col1 = b.col1
from tablea a join tableb b on a.id = b.id and a.col2='value' and b.col2='value'
inner join
(
select top 10000 from tablea a inner join tableb b on a.id = b.id  and a.col2='value' and b.col2='value'
  where a.col2='value' <>  b.col2='value'
) as c on c.id = a.id  






  where b.col1 <> b.cola
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
LVL 42

Expert Comment

by:dqmq
ID: 36590875
Pls ignore last line.  
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 500 total points
ID: 36590906
>>the join is as simple as
update a set col1 = b.col1 from tablea a join tableb b on a.id = b.id and a.col2='value' and b.col2='value' <<
Are the appropriate columns indexed.  If they are not It could take you several days to complete.  Once you have it indexed correctly consider re-writing it as follows:
UPDATE  a
SET     col1 = b.col1
FROM    tablea a
        JOIN tableb b ON a.id = b.id
WHERE   a.col2 = 'value'
        AND b.col2 = 'value'
0
 
LVL 5

Author Comment

by:25112
ID: 36601461
thanks I followed that logic and also indexed.. here is another similar query to update 23M records. Indexed NC on the join columns... but there is no other condition other than the join itself.. how could it be done to iterate 10K each time?

UPDATE A
SET A.Col1 = B.Col1
FROM A JOIN B ON A.ID = B.ID
0
 
LVL 42

Assisted Solution

by:dqmq
dqmq earned 1000 total points
ID: 36616838
Update A
Set A.col1 = B.col1
from A join B on A.id = b.id
join
(Select top 10000 A.* from A  join B on A.id = B.id
 where a.col1 <> b.col1 ) as c  on c.id = a.id
0
 
LVL 5

Author Comment

by:25112
ID: 36639850
that is neat..

if a.col1 or b.col1 is null, will it need some tweaking?
0
 
LVL 5

Author Comment

by:25112
ID: 36709395
dqmq, would you then put the update within a while loop?
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft provides a rich set of technologies for High Availability and Disaster Recovery solutions.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

599 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question