30 Jan 2006

Slow SQL Server Delete

Just wanted to share a solution with you. Slow SQL deletes had begun to make an application perform poorly.

I had optimized a delete sproc as much as possible using WITH (ROWLOCK) and all the other performance tricks I could find. Showing Execution Plan in Query Analyser shed no light on the problem - it reckoned the delete was putting little load on the server. However, deleting 500 rows from a table was taking 40 seconds!

Turns out that if you have referential integrity set up between your tables (i.e. foreign key relationships with Foreign Key Constraints enforced), you need to ensure that all foreign key columns in your tables have an index. Otherwise, when you delete a record from a primary key table, referential integrity checks will cause table scans on the referring foreign key tables - and scans are terribly slow compared to index lookups. Sure enough I had neglected to put an index on one of the referring tables; when fixed the same query took less than 2 seconds.

EXAMPLE

TABLE: Customer

CustomerID (Primary Key)
Name
Address etc.

TABLE: Order

OrderID (Primary Key)
CustomerID (Foreign Key - Relationship with Customer table - THIS FIELD SHOULD BE INDEXED)
Order Details etc.

3 comments:

  1. Hi Zootius!

    Thanks so much for sharing for this tip. It solved my problem.

    I think it is a shame that, after so long, Microsoft has not still recognized that foreign keys should be treated as implicit indexes

    Júlio Nobre

    ReplyDelete
  2. Thanks! This post just saved me a lot of headache.

    ReplyDelete
  3. Anonymous12:40 pm

    Thank you, you really helped me out!
    That was exactly the problem I had no clue how to solve.

    ReplyDelete

Comments are very welcome unless you're a spammer, in which case you should probably kill yourself.

If I helped you out today, you can buy me a beer below. Cheers!