Dropping and recreating indexes for performance reasons?

Question

I'm maintaining Somebody Else's Code. There's a stored procedure that's run once a day or week (depending on config), that migrates a bunch of records from an active table to an archive. But it's not a true archive, since there are several live processes that include the archive table in SQL queries, which is properly indexed for the purpose.

Now the history of this story is that the archive table grew and grew, and the archiving SP started taking a very long time. So Somebody decided it would be a good idea at the beginning of the SP to drop all the indexes on the archive table and recreate them at the end. Of course, recreating them also takes eons, but arguably not as long as inserting the new records with the indexes in place.

This situation just screams at me: "THIS IS SINFUL!" It just seems wrong, wrong, wrong to be screwing around with indexes at runtime. I'm sure this is not an uncommon situation, so what is the "correct" way of handling a situation like this?

Answer 1

Instead of dropping the indexes, I'd recommend disabling them then enabling them. Preferably through a cursor (ignoring the clustered index) so that if an index is added or removed from the archive table the stored procedure doesn't need to be modified.

This is pretty standard when doing large data warehouse loads, which is basically what you are doing.

UPDATE: Scrub that. With the very small workload that you are doing just insert the records without dropping and readding the indexes. If you were moving hundreds of millions of rows then removing the indexes would be worth it.

Answer 2

I think the clue to the answer is in the remark in my question:

But it's not a true archive, since there are several live processes that include the archive table in SQL queries.

If the archive table is being actively used for queries, then it's not an archive, and the whole point of archiving records has been lost.

This whole issue comes back to a bad design decision. Originally the records were supposed be be archived by groups of completed data - in our domain, that's "orders" (as in orders placed by a customer). So when an order was completed, paid for, and no longer a going concern, it and all its related data would be archived. Then somebody came along and decided that we needed the ability to partially archive an order, since some orders had one or two items that dragged out for months but 99% of the order was complete, so they wanted to push that 99% into the archive. But in order to get all the information associated with that order, you'd need to go into the archive. So they created a view that straddles the live and archive tables for the shop-floor app to use. And of course, as soon as you do that, you've violated the concept of an archive, i.e. that it should be information that you don't need to query on a regular basis. And once you've done that, you inevitably start going down the path of kludges like this that dramatically affect the performance of your mission-critical software systems.

So in this particular case, I'm going to go down the route of fixing the original bad design decision, and stop with this nonsense of partial-archiving. Then the archive can do what archives are supposed to do (i.e. sit back and enjoy retirement), and leave the active queries working only on the active tables.