Delete from tablename takes abnormally long

Question

I've a table in sql server 2005 with a chaild table that has millions of records in it. Whenever I issue a delete command even with a where clause, it takes abnormally long to execute it. Is it bacause the size of the child table that is causing this delay? What are the ways to make the delete query faster?

thanks, sweta.

Answer 1

The usual suspects:

• Trigger?
• Index on column(s) in the WHERE clause?
• Index on FL column(s) in child table FK column?
• Cascade delete etc: see index points

Also:

• log file growing?
• ...

Edit, after comments: You need indexes...

Answer 2

It sounds like it may be doing a table scan on the child table. Make sure that the joining column has an index on the child table.