Delete Performance: Disable vs Drop constraints

  • Lidou123

    Hall of Fame

    Points: 3054

    Hello

    I'm doing a huge task of deleting data in a large table of 300 million rows.
    This table contains 5 Foreign Keys.
    I tried a first test that gave me catastrophic results.
    - Do you have any performance tips to perform this task?

    In addition, I want to remove these foreign keys.
    Do you think it is more efficient to disable/enable or drop/recreate?

  • Jeff Moden

    SSC Guru

    Points: 996655

    Lidou123 - Monday, September 17, 2018 11:42 PM

    Hello

    I'm doing a huge task of deleting data in a large table of 300 million rows.
    This table contains 5 Foreign Keys.
    I tried a first test that gave me catastrophic results.
    - Do you have any performance tips to perform this task?

    In addition, I want to remove these foreign keys.
    Do you think it is more efficient to disable/enable or drop/recreate?

    How many of the 300 million rows are you deleting?

    --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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Lidou123

    Hall of Fame

    Points: 3054

    Jeff Moden - Tuesday, September 18, 2018 3:43 PM

    Lidou123 - Monday, September 17, 2018 11:42 PM

    Hello

    I'm doing a huge task of deleting data in a large table of 300 million rows.
    This table contains 5 Foreign Keys.
    I tried a first test that gave me catastrophic results.
    - Do you have any performance tips to perform this task?

    In addition, I want to remove these foreign keys.
    Do you think it is more efficient to disable/enable or drop/recreate?

    How many of the 300 million rows are you deleting?

    Hi all

    It's about 150.000 rows by week
  • Jeff Moden

    SSC Guru

    Points: 996655

    In that case, that's only a little more 600,000 rows per month.  Perhaps partitioning by month would help in that you could simply and nearly instantly SWITCH out the oldest month of data.

    --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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeffrey Williams

    SSC Guru

    Points: 88547

    Jeff Moden - Thursday, September 20, 2018 12:22 PM

    In that case, that's only a little more 600,000 rows per month.  Perhaps partitioning by month would help in that you could simply and nearly instantly SWITCH out the oldest month of data.

    But would that really work with multiple related tables that need to be deleted/switched out at the same time?

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Jeffrey Williams

    SSC Guru

    Points: 88547

    Lidou123 - Monday, September 17, 2018 11:42 PM

    Hello

    I'm doing a huge task of deleting data in a large table of 300 million rows.
    This table contains 5 Foreign Keys.
    I tried a first test that gave me catastrophic results.
    - Do you have any performance tips to perform this task?

    In addition, I want to remove these foreign keys.
    Do you think it is more efficient to disable/enable or drop/recreate?

    When you say catastrophic results - what actually happened?  And what do you mean when you say you want to remove the foreign keys?  Why would you want to remove constraints that insure the data in those columns are valid - or that related tables do not have orphaned rows?

    Either way - to delete without killing your system you need to delete the data in batches that are small enough to not block other users and small enough so you do not explode the transaction log and fill the drive.

    I would not recommend disabling/rebuilding or dropping/recreating foreign key constraints to improve delete performance.  You can disable non-clustered indexes for the delete operation and then rebuild all indexes after the delete - which you are going to have to do anyways because the delete is going to leave large gaps across all indexes.  That may improve the delete performance but I would not think it would be enough to allow you to delete everything in one pass...

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Lidou123

    Hall of Fame

    Points: 3054

    Thank you for your answers.

    After testing disable/enable vs drop/create foreign keys I had the same performance.
    Finally we prefered the 'drop/create' 

    Thank you

Viewing 7 posts - 1 through 7 (of 7 total)

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