Delete Performance: Disable vs Drop constraints

Question

Delete Performance: Disable vs Drop constraints

Lidou123

Hall of Fame

Points: 3186
More actions
September 17, 2018 at 11:42 pm

#372801

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?

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

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

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 1

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.

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

Lidou123 Hall of Fame Points: 3186 More actions · Answer 2

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: 1003863 More actions · Answer 3

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.

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

Jeffrey Williams SSC Guru Points: 90252 More actions · Answer 4

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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

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

Jeffrey Williams SSC Guru Points: 90252 More actions · Answer 5

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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

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

Lidou123 Hall of Fame Points: 3186 More actions · Answer 6

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