HUGE Database Update (> 550 million rows)

Question

HUGE Database Update (> 550 million rows)

Viewing 2 posts - 16 through 16 (of 16 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Brian.Dunat (1/23/2010)
This is the proper clustered index and it makes up the primary key on the table. This is a wealth management database, so the data that will be inserted again post this update will all be logically ordered in accordance with the clustered index.

A 7-column wide clustered index? That is not going to be doing nice things to your nonclustered indexes. Are you sure that's the best place for the cluster? (Clustered index does not have to be on the pk)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 2

Brian.Dunat (1/23/2010)
Jeff,
This is the proper clustered index and it makes up the primary key on the table. This is a wealth management database, so the data that will be inserted again post this update will all be logically ordered in accordance with the clustered index.
We are near rollout of this system and we are anticipating that the database will be offline while these updates take place (no SELECTs), so that is why I think the best approach will be to drop that clustered index, create the nonclustered index on the key column, perform the update, drop that temporary index and recreate the clustered index. Once that is done and we verify the updates, then we will be able to bring the system back online.
Thanks again!
Brian

Understood... but please understand the maintenance nightmare you will achieve if the clustered index isn't in the same order as most of the inserts. The PK does not have to be the clustered index.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)