3

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.

sweta Jha
  • 33
  • 4
  • @sweta Jha, imagine that you have to delete a name from the phone book, but it is not indexed (all the names are in random order) how long would it take you to find the name to delete? wouldn't it be faster to delete a name if there was an index (they were all in sorted order). When something runs slow in a database, the first thing to check is.... you guessed it... is an index being used? ask more questions if you need help with the indexes... – KM. Feb 26 '10 at 13:51

2 Answers2

3

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

gbn
  • 422,506
  • 82
  • 585
  • 676
  • Triggers - none Index on column(s) in the WHERE clause - none Cascade delete - none This is the delete command.. delete from sal_process_details where sal_process_master_id in (select sal_process_master_id from sal_process_master WHERE Process_month = @month and process_year = @year and employee_id in(select employee_id from sal_process_temp_emp)) – sweta Jha Feb 26 '10 at 06:28
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.

Adam Ruth
  • 3,575
  • 1
  • 21
  • 20
  • Is there a way i can temporarily suspend the foreign key constraint between the two tables and enable the constraint after deletion is complete. pls let me know if there is some other work around to this problem. – sweta Jha Feb 26 '10 at 07:14
  • http://stackoverflow.com/questions/159038/can-foreign-key-constraints-be-temporarily-disabled-using-tsql – Adam Ruth Feb 28 '10 at 21:07