Deleting parent table where related child table is empty is SLOW
-
I've got a problem and a work-around here. What I'd really like is to know why I have the problem in the first place.
I have a table with 190,000 rows or so, and a child table with maybe four times that. There's a foreign key constraint between them, defined across two columns. It's the primary key in the partent table, and the first column (very high cardinality) is part of an index on the child column. (More detail available, but this should suffice.)
I delete all rows from the child table. Takes a few seconds, pumps up the transaction log file, ho hum.
I attempt to delete all rows from the parent table. The command takes forever to run (I gave up after 8 hours). Analysis using query plans and Profiler show some kind of bizarre and extensive locking-related activity going on with the child table -- the empty child table. Huh? This happens ever when I delete 20-50 rows at a time--takes a minute or so (DELETE <table> where <filetering criteria on index produce 20 rows>
.The work around was to disable the foreign key (ALTER NOCHECK CONSTRAINT), and enable it after the delete. Shazam, the delete takes 2 seconds.
So: Why would the presence of an empty child table cause a deletion on a parent table to bog down? What is it doing in there, anyway?
(This is in SQL 2005 sp2a, though I have one ad-hoc reference that this may also happen in SQL 2000.)
Philip
P.S.
-
Sounds strange, not run into that one before. Have you tried to update the statistics on the child table after the delete of the records in it but before you delete from the parent table?
Is there a primary key on the child table? If not could that be an issue?
-
Updating statistics had no impact, and there is a primary key on the table.
Philip
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply