Need help with deleting rows & table contraints
-
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. I apologize for the long post but I hoped to make this as clear as it can be. I am not sure if I did a good job at that.
Thanks!
John
-
I don't think it is the constraints that are causing the problem here. Here is what I would try.
Create a nonclustered index on the CycleID and ExtractTypeId fields to use for the delete.
Drop the clustered index
Delete the records
Re-Create the clusterd index.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB EngineerThis posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
-
Gary,
I tried what you suggested and no luck. There is a primary key constraint that references the clustered index. I am not sure if dropping the clustered index would make a difference though. When I copied the data to a new table, I created a clustered index on it before I ran the delete statement. It deleted like expected with the clustered index. Thanks for the help!
John
PS I didn't design this database. One of our App Dev guys did, I am just trying to troubleshoot this for him.
-
Hi,
I have had similar experiences from time to time.
What I usually end up doing is:
1. ALTER TABLE refTable DROP CONSTRAINT refConstr
2. DELETE FOM referencedTable
3. ALTER TABLE refTable WITH NOCHECK ADD CONSTRAINT refConstr( ... )
The constraints can be scripted from QA.
UPDATE STATISTICS is also a good idea after modifying the number of rows in the table.
**********************************************
Now You could try this ( I havent tried it yet for deletes )
This enables / disables all constraints for a table
ALTER TABLE [dbo].[TableThatRefAdresses] NOCHECK CONSTRAINT ALL
DELETE FROM Adresses....
ALTER TABLE [dbo].[TableThatRefAdresses] CHECK CONSTRAINT ALL
************************************************
Run for example: sp_help '[dbo].[TableThatRefAdresses]'
where You can see if a particular constraint is enabled/disabled
/rockmoose
You must unlearn what You have learnt -
Put indexes on the FK-columns of your dependant tables (if multi-column, provide exact order and sorting !). (1 index per FK !)
If you have cascading deletes enabled, you can delete from the parent table.
If you have not enabled cascading deletes, you'll need to delete the dependant rows yourself before you try to delete the parent rows.
SQLServer will still check if the parent-key is in use at the child tables !
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
It seems that we have found the reason for the deletes to take so long. The statistics were not up to date. As soon as we updated the statistics, the delete statement went from 5 hours down to 2 minutes. The time we expected it to run. We now have a nightly scheduled job to update the statistics for that database. Just thought I would share what I found out.
John
-
Thanks for the feedback.
FYI : we have auto-update-statistics on all our sql7- and sql2k-db.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
So do we but it didn't seem to be working correctly. This way we know for sure that it is going to happen.
John
-
I just checked a couple of servers and we also run extra sp_updatestats every 4 months as well as dbcc updateusage because auto-stats get inaccurate after a while.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply