Trying to speed up very large updates to tables

[ccuster]

I have two tables one 17 million records, and the other 10 million records. I am updating fields in the 17 million record db. no matter what kinds of locks or how I break the data down I can only get around 200,000 updates an hour. I have created indexes on the lookup fields.

Does anyone have any experience or suggestions on what would make this quicker. I am using a Power Edge 2300, 1 gig ram dual 450 CPU's, and SCSI Raid 5.

 

[culshaja]

Make sure that the indexed feilds are not the ones being updated as this will slow down the updates. If the feilds being updated are varchar then you might think of changing the feilds to char. This means that blocks don't have to be moved about/recreated and populated by increasing column data size as varchar only uses space for the data, and so if you increase that data size then things have to be moved about. This does have disk space implications though.

If you can do it in SQL Server (I can't remember) you might want to change the fill level so that a block isn't used more than 60% so that updates can occur if you don't change the varchar fields.

James James Culshaw
jculshaw@active-data-solutions.co.uk

http://www.active-data-solutions.co.uk

http://www.brainbench.com/transcript.jsp?pid=193305

 

[calahans]

Could you post the SQL update?

Cal

 

[jnicho02]

Questions I normally ask myself are things like: Is this going to be a regular event or a one-off?
Sometimes for one-off events you just have to bite the bullet....but if it's going to be a regular event then you had better find a solution.

James' suggestions are good...The main thing to look at is whether rows will need to be physically moved around to maintain the clustered index, and whether the gaps are big enough to fit those rows in without having to shunt everyone up one.

* You could look at the sequencing of updates, and make sure that they are done in the same order as the data is sorted in the table (clustered key).

* In extreme circumstances, people have been known to bcp the whole lot out, amend the data via a C program, sort it into the correct order, then bcp it back in again....but that isn't recommended for the faint hearted. My Home -->

http://www.geocities.com/jnicho02/

 

[ccuster]

Thank you all for your support,

The query I am using is:

Update T1
Set T1.Field1 = T2.Field1,
T1.Field2 = T2.Field2...
From T1, T2 with (nolock)
Where T1.IDField = T2.IDField

The Updated fields are not indexed and both the IDFields are indexed, on T1 it is nonclustered and on T2 it is Clustered. There are no varchar type fields involved.

I also feel the time to bcp out amend the date then bcp it back in would be time consuming as well. As well as more chance for errors/ any error at this amount of records costs a lot of time(my own experience speaking).

I was just making sure I wasn't making any bone-headed mistakes in my logic.

Again thanks, it is great to be a part of support, and although I am new to this forum I will also add help through my experiences.

 

[jnicho02]

You are correct about the bcp. It could be very useful to sort the rows of a very large table before putting a clustered index on. My Home -->

http://www.geocities.com/jnicho02/

 

[calahans]

Your updating one table from another, this is rather expensive. If you need to do this daily, then I would review your schema. Maybe you should update both tables at the same time, instead of updating one and then later updating the other table en masse later.

Also why do you need to update the table from the other, why can't you read the data from a join. Why have data redundancy - thats dangerous.

Also run your SQL in the Profiler, maybe you are doing table scans for each update, maybe the analyser is having problems query flattening..

Cal