Dropping and Re-creating Indexes

Question

Post reply

Dropping and Re-creating Indexes

Michael Lee-169622

SSCommitted

Points: 1721
More actions
December 29, 2005 at 12:33 pm

#188763

I have a table with over 3,000,000 records. This table has 10 indexes. I have a script which loads the table into a temp table, eliminating any duplicate records, then truncates the original table, and reloads the original table.

My problem is performance. When I run the script, it takes 6 hours to run. I have discovered that if I delete all the indexes, run the script, then recreate the indexes, it takes about 18 minutes to run.

My Question: Is there any repercussions to dropping and re-creating the indexes on a regular basis? Like daily?

Shalom!,

Michael Lee

Viewing 4 posts - 1 through 3 (of 3 total)

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

PW-201837 SSC-Insane Points: 20805 More actions · Answer 1

There shouldn't be any repercussions. Just remember to use the most optimal Drop/Create sequence.

Drop non-clustered first, then clustered

Re-create clustered first, then non-clustered

The only question really is, why is it not possible to identify the dupes and delete only that small subset of records, instead of copying all to a temp table & re-loading ?

Michael Lee-169622 SSCommitted Points: 1721 More actions · Answer 2

There has been much discussion around dropping duplicate records, especally when they are not necessarly dupes. In my case, there is an Adjustment Date and Adjustment Time that for some reason, get updated, even though the rest of the record is not changed. This is not my party, I am drawing my data from a Corporate data warehouse. I need to drop all previous records and save the latest.

I found the most efficient way for my situation is to create a temp table with a unique index and the ignore_dup_key. I then insert my records sorted by my key and a desending Adjustment Date and Time. I now have only the latest records that I can copy back to my orginal table.

Thanks for your help!

Shalom!,

Michael Lee

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

Yup, your method works just fine (but what a pain with all of those indexes). And, deleting the "dupes" probably wouldn't be any faster because of the indexes.

And, to answer your original question, there's nothing going to happen with dropping/recreating indexes except maybe the performance gain you've realized.

I'm still thinking that if you add your unique index with the ignore_dub_key to THAT table, you wouldn't have to do this anymore. I know it's not your party but blow the horn once and see who comes out. Ask them if you can add the index.

Then, all you'd need to do in the future is to run DBCC REINDEX once in a while...

--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)
Intro to Tally Tables and Functions