Referential constraints - Delete slow

Does anyone have experience with cascade delete being slow on large
databases?
It appears that the cascade delete is not making use of the existing
clustered indexes.
If I create statements deleting the same records from the 20 referenced
child tables (with children of their own) using the foreign key column of
the parent table the delete occurs in a few seconds vs over a minute for the
constraint to delete the record and all chldren. Even if the parent has no
children it takes ovr a minute for it to scan the children for potential
orphans. Since I know no way to observe the steps that the constraint is
performing I can only assume that for some reason it is not using the
existing indexes on the table.Gene
Did you have on referensing table an index?
Have you tried to run show plan of the query to see what is going on?
Personally I don't have any problem with perfomance in my VLRD when I
perfom deletion.
"Gene Black" <geblack@.cox.net> wrote in message
news:#LbuG5SCEHA.1548@.TK2MSFTNGP12.phx.gbl...
> Does anyone have experience with cascade delete being slow on large
> databases?
> It appears that the cascade delete is not making use of the existing
> clustered indexes.
> If I create statements deleting the same records from the 20 referenced
> child tables (with children of their own) using the foreign key column of
> the parent table the delete occurs in a few seconds vs over a minute for
the
> constraint to delete the record and all chldren. Even if the parent has no
> children it takes ovr a minute for it to scan the children for potential
> orphans. Since I know no way to observe the steps that the constraint is
> performing I can only assume that for some reason it is not using the
> existing indexes on the table.
>|||When I use show query plan it appears that many of the clustered indexes are
not being used. I see sorts and hash joins happening vs when I construct the
deletions using joins the clustered indexes are used.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eTKja2aCEHA.3152@.TK2MSFTNGP10.phx.gbl...
> Gene
> Did you have on referensing table an index?
> Have you tried to run show plan of the query to see what is going on?
> Personally I don't have any problem with perfomance in my VLRD when I
> perfom deletion.
>
> "Gene Black" <geblack@.cox.net> wrote in message
> news:#LbuG5SCEHA.1548@.TK2MSFTNGP12.phx.gbl...
of
> the
no
>|||After testing it appears that if I overlay a non-clustered index on top of
the existing clustered index the delete operation using the RI constraint
with cascade delete performs the same as the manually constructed delete.
The query plan using showplan looks almost identical but the performance
difference is extensive. This does not seem like a necessary solution as the
columns are already identified and used in the clustered index on the table.
I am still perplexed by the fact that it does not perform as expected until
a nonclustered index is laid over top of the clustered index.
I will have to examine it some more, maybe one of the 60 referenced tables
is missing a clustered index, since those have been applied over time
manually while the overlays were created by automated script following the
relationship tree.
"Gene Black" <geblack@.cox.net> wrote in message
news:%23LbuG5SCEHA.1548@.TK2MSFTNGP12.phx.gbl...
> Does anyone have experience with cascade delete being slow on large
> databases?
> It appears that the cascade delete is not making use of the existing
> clustered indexes.
> If I create statements deleting the same records from the 20 referenced
> child tables (with children of their own) using the foreign key column of
> the parent table the delete occurs in a few seconds vs over a minute for
the
> constraint to delete the record and all chldren. Even if the parent has no
> children it takes ovr a minute for it to scan the children for potential
> orphans. Since I know no way to observe the steps that the constraint is
> performing I can only assume that for some reason it is not using the
> existing indexes on the table.
>