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.

That execution plan shows a table scan on OrderDetails, and on two other small tables. Do you have a self-referencing FK on that table, and is the FK in that case on a non-indexed column? If you share the schema for the table and the related tables along with the actual execution plan (in .xml format) it will be easier to say exactly what is the cause... — KristoferA, Oct 15 '09 at 10:49

score 0 · Answer 1 · answered Oct 15 '09 at 08:53

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)

score 0 · Answer 2 · answered Oct 15 '09 at 08:56

0

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.

answered Oct 15 '09 at 08:56

Jeremy French

11,707
6
46
71

score 0 · Accepted Answer · edited May 23 '17 at 12:01

0

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.

edited May 23 '17 at 12:01

Community

1
1

answered Oct 16 '09 at 07:24

sirrocco

7,975
4
59
81

Large number of "logical reads" on single row delete

3 Answers3