update duplicate rows in table with over 160 million rows

Question

Post reply

update duplicate rows in table with over 160 million rows

Viewing 5 posts - 16 through 19 (of 19 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

For the force index part >>

Select id from dbo.SysObjects

Select id from dbo.SysObjects with (index (ncSysObjects))

you can also use the indid of the index instead of its name... but the name is safer that its smaller id if you want my opinion.

Colin MacKenzie SSC-Addicted Points: 480 More actions · Answer 2

I can't add an identity column on the table as there's already one there... however I've tested this locally and it's significantly faster than I expected... I think I have a couple new steps to run with, just have to wait for the rollback to happen now.

Thanks a TON everyone!

I'll post new response times out of this.

Cheers

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Alright, you could use the current identity then... Of course if you have big gaps, you'll have a few updates where next to now rows will be updated. Also make sure that the update is the last operation. I think the @@rowcount variable is reupdated even with a simple if... and the loop would break early.

Carl Federl One Orange Chip Points: 25384 More actions · Answer 4

<When I load new users in ...non-current values are then removed from the table and inserted into a history table).

>

If this is the real process, try changing the order of the process with a input table which would eliminate the need to perform any updates:

1. Create table ContactDetails_Stage with same columns as ContactDetails.

2. Insert the new rows into ContactDetails_Stage

3. Copy to History with:

insert into ContactDetails_History (...)

select ContactDetails.<columns>

from ContactDetails

join ContactDetails_Stage

on ContactDetails.ContactId = ContactDetails_Stage.ContactId

4. Delete from Live with:

Delete ContactDetails

from ContactDetails_Stage

where ContactDetails.ContactId = ContactDetails_Stage.ContactId

5. Add the new to Live with:

insert into ContactDetails

select ContactDetails_Stage.<column>

From ContactDetails_Stage

6. Clean-up

Truncate table ContactDetails_Stage

HTH

SQL = Scarcely Qualifies as a Language

Colin MacKenzie SSC-Addicted Points: 480 More actions · Answer 5

I have actually considered doing exactly this, however there are currently 15 or so other procs that actually insert into ContactDetails table, so I'd rather not have to go through each of them to make changes...

Cheers