Cascading Delete Problem

I have three tables that equate to a customer - order - order items
scenario.
The Customer table has 100,000 rows
The Order table has 3 million rows
The Order items has 30 million rows.
I have the primary and foreign keys all defind and the appropriate indexes.
I have cascading deletes on the foreign keys between all three tables.
It takes over a minute to delete a customer. If I remove the cacading
delete, and delete from the order items then orders then customers it takes
seconds.
Why does the cascading deletes take so long?
Using SQL2000 sp3a
Thanks for any insight!On Thu, 03 Feb 2005 19:35:25 GMT, Howard Carr wrote:
>I have three tables that equate to a customer - order - order items
>scenario.
>The Customer table has 100,000 rows
>The Order table has 3 million rows
>The Order items has 30 million rows.
>I have the primary and foreign keys all defind and the appropriate indexes.
>I have cascading deletes on the foreign keys between all three tables.
>It takes over a minute to delete a customer. If I remove the cacading
>delete, and delete from the order items then orders then customers it takes
>seconds.
>Why does the cascading deletes take so long?
Hi Howard,
Is your primary key (or unique) constraint for the OrderItems table
defined as (OrderID, CustomerID)?
Try changing it to (CustomerID, OrderID). It will speed up the deletion of
a customer, but it will slow down the deletion of an order. If both
deletion happens frequently enough to need good performance, you might try
adding an extra index, so that both orders are present.
Note that changing indexes might affect performance of all other queries
as well.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)