Need help with deleting rows & table constraints

[laker42]

We have a table, Addresses, that contains around 2 million rows. We have 3 other tables that have foreign key contraints that reference the Addresses table. Each of those 3 tables contain around 2 million rows. The Addresses table has a primary key of CycleId, ExtractTypeId, AcctNo. It has a clustered index defined on these three fields. We are trying to do a delete in the Addresses table that should only delete 11,000 rows in the table. We first deleted all of the related rows in the other 3 tables, no problem yet. When we run the following delete statement: Delete Addresses Where CycleId = '200407' and ExtractTypeId = '2', this query runs for over 4 hours before finishing. We cannot figure out why it is taking this long to delete only 11,000 rows. Here is what I have tried to troubleshoot this:
1. Ran DBCC IndexDefrag - made no difference
2. Rebuilt the index - made no difference
3. Turned on cascade deletes for each of the relationships - made no difference
4. Performed an select into new table and ran the query with no contraints referencing the table - the delete finishes in less than 1 second!

So what is this telling us? How does SQL Server work with deletes & constraints? Is there a way we can insert a hint into the delete statement to let it know that it does not have to check every row against all the constraints? I have been looking in BOL and haven't been able to find any good answers. We haven't had a lot of experience with constraints so nobody here is really an expert on this. Any ideas on how we can get this delete statement to be more efficient and not take as long to execute? Are we stuck with this running this way? I know others have done this before and I can't believe that they are settling for waiting on a delete statement to delete 11,000 rows in 4-5 hours. We would really appreciate any help you may be able to offer.

Thanks!
John

 

[john76]

I don't have anything to try this out on myself, but according to BOL you can temporarily disable the constraints by using something like this:

Alter Table Addresses NoCheck Constraint MyConstraint

Delete From Addresses
  Where CycleId = '200407'
    And ExtractTypeId = '2'

Alter Table Addresses Check Constraint All

Hope this helps,
John

 

[laker42]

Hi John,
Thanks for the suggestions but BOL also says that this only works for Insert & Update statements. I did get excited when I first read that in BOL but the excitement didn't last long. Plus doing this really negates the reason to have constraints in the first place.

Thanks!
John