SQL Server Performance Forum – Threads Archive

Deleting 230 rows takes over 3:30 SOLVED

Hi, I’m experiencing a problem when I issue a simple DELETE query on one my database table.
The table haa about 90 000 records and the DELETE affects about 230 rows. It takes over 3 minutes 30 to execute. I never had a problem before with that query. Any tips would be appreciated. Thanks
Max

One idea is to remove all indexes, delete rows and create indexes again.
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

any delete triggers on the table?

Sounds like the stats are out of whack and the delete is probably not using an index where it used to… There are a ton of variables that can affect performance in this manner (works one day, not the next).

another cause could be other tables having a foreign key back to this table, and the table with the FK does not have an index on the FK column
always check the execution plan for deletes.
also check sysprocesses to see if the DELETE query is blocked, ie, it could be a really fast query, but if it is blocked, then it could take forever

hi,
You have not provided enough information about what all are discussing here.
So all solution would be like shot in the Dark. So please respond with all details

Thanks for all the answers, i’ll look into them and post back if I figure something out.

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by joechang another cause could be other tables having a foreign key back to this table, and the table with the FK does not have an index on the FK column always check the execution plan for deletes. also check sysprocesses to see if the DELETE query is blocked, ie, it could be a really fast query, but if it is blocked, then it could take forever. <hr height="1" noshade id="quote"></blockquote id="quote"> Dead on (the foreign key part) !! Thanks a lot [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />].

Thanks for the feedback.
Luis Martin
Moderator
SQL-Server-Performance.com Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
Leonardo Da Vinci Nunca esperes el reconocimiento de tus hijos, eso ocurrirÃ¡ luego de tu muerte
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

]]>

Deleting 230 rows takes over 3:30 **SOLVED**

Deleting 230 rows takes over 3:30 SOLVED