Large number of "logical reads" on single row delete

Question

When deleting a row by its PrimaryKey from a table, I get about 44472 logical reads. Now the table has 5-6 child tables that link their ForeignKeys to the PK of the table I want to delete from.

I'm not sure what to do to improve the performance of the delete.

Any suggestions ?

Edit : I added the queryplan for the delete

http://img384.imageshack.us/img384/6255/deleteexecutionplan.png

Edit : I found a solution (not sure if it's the ideal solution)- it's in the response bellow.

Answer 1

This answer solved the problem, now deletes work like a charm. I'm not sure if there are any downsides I should be aware of.

Answer 2

Look at the query plan for the single row delete.

I think you will find that a table scan is being done on one or more of the "child" tables. If so consider putting an index on the ForeignKey in on that child table(s).

(Otherwise please add the query plan to your question)

Answer 3

Do you have FK constraints?

The options I can think of are

• Add indexes to the FK columns in the child tables.
• Remove the constraint (which would risk having orphaned rows).
• Try reducing the number of child tables.