Results 1 to 5 of 5

Thread: Can you speed up readding constraints to a table

  1. #1
    Join Date
    May 2010
    Posts
    2

    Cool Can you speed up readding constraints to a table

    Hi All,
    I have created a number of stored procedure for archiving inactive records to another database. To do this I do the following:-
    1. Remove the foreign keys and indexes from the tables I am going to archive (as the tables are large this is the fastest way)
    2. Search for the inactive records, move them to the Archive database then delete from the original tables
    3. Re-add the foreign keys and indexes to the tables.

    My question is as follows, some of the tables are pretty large (>2 million records) and when I go to re-add the foreign keys it is pretty slow to do this. I am using the following to re-add the constraints
    ALTER TABLE [FK_TBL] WITH CHECK ADD CONSTRAINT [Constraint name] FOREIGN KEY([FK_Column]) REFERENCES [PK_TBL]([PK_Col])

    Is there a quicker way to readd the constraints to the table or is this the only way? I need to make sure that all the remaining Active information is not orphaned that is why I am using with check

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    Instead of dropping and recreating, can you use disable and enable using ALTER TABLE .. NOCHECK

  3. #3
    Join Date
    May 2010
    Posts
    2
    Hi Skhanal,
    I have tried disabling and enable the constraints but it is still slow and a lot of the tables have a lot of records so I found on some other sites that dropping and readding the constraints would be the fastest way for moving large amount of data.

  4. #4
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    If you are sure about data validity you can create the constraint with NOCHECK option. The alter the constraint to CHECK for new data.

  5. #5
    Join Date
    Sep 2005
    Posts
    168
    How often is the archive process executed and how many records are moved (in average) to the archive db?
    In case of a frequent archive process it might be better to approach archiving on a per record (or small batch) basis.

    One note regarding the steps of the archiving process
    1. Remove the foreign keys and indexes from the tables I am going to archive (as the tables are large this is the fastest way)
    2. Search for the inactive records, move them to the Archive database then delete from the original tables
    3. Re-add the foreign keys and indexes to the tables.
    It might be better to keep the indexes for spotting/marking the records to be archived (in case indexes help in doing so) and drop them just before deletion :
    1. Mark records to be archived
    2. Move records to archive db
    3. Remove indexes and fk's
    4. Delete archived records from the live db
    5. Recreate indexes and fk's

    --HTH--

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •