Cascade delete slow on SQL 2000 sp3

Question

Cascade delete slow on SQL 2000 sp3

GeBlack

Ten Centuries

Points: 1363
More actions
March 13, 2004 at 12:12 pm

#66349

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 over 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.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

GeBlack Ten Centuries Points: 1363 More actions · Answer 1

On thinking about this could cascade delete be doing record based operations vs set based operations? (since my own constructed deletes would be using set based ops)

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 2

- are there any indexes on the foreign-key-columns to support the delete-join ? (with all child-tables)

- If yes, are the columns in the same columnorder and sortorder(asc/desc) as the parent table ?

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

GeBlack Ten Centuries Points: 1363 More actions · Answer 3

yes there are

although the pk on the parent isn't clustered

when I overlayed a non-clustered index on top of the ci, performance improved and was similar to the manual deletes performed without RI (by turning off the constraints using nocheck and constructiong individual statements joining acrossed the relationship columns chain)

some of the tables are children of multiple parents though and in that case the fk's are multiple column clustered indexes with higher selectivity column being the lower ordinal

performance did seem to improve if I overlayed a single column non-clustered index on top of the clustered indexes

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 4

as you have noticed, RI should have an index per FK-definition (unless the overlap is matching columnorder and sortdirection), otherwize indexes might not be used for RI-purposes.