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?