SQL Server Performance Forum – Threads Archive

DELETE is running very slow

I have a table which has about 80,000 records. Table has a composite key of quest_id, quest_country and quest_year. When I run a delete statement: DELETE FROM tableX WHERE quest_country=’US’ and quest_year=2004 It is taking for ever to execute. This statement should delete about 2000 rows. I also added an index for quest_country, quest_year and that didn’t help. I am not sure where to look. Appreciate any tips. Thanks.

if you are deleting a large number of rows, and the execution plan shows a nonclustered index seek, you might try forcing a table scan
WITH (INDEX(0))
this could be faster than the index seek plan, see my paper on large data ops for the reason why

So what will the delete statement look like?
I tried:
DELETE FROM tableX WHERE quest_country=’US’ and quest_year=2004 WITH (INDEX(0)) and it didn’t work … sorry I have not used WITH (INDEX(0)) before. Thanks for your time.

Delete from Table with (index(0)) ……
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.

Be sure you show the execution plan in the Query Optimizer. If it is doing a Table Scan already to determine the rows to select the index you added to help this out will actually slow your performance. Because not only does it have to remove the data, and remove entries in the previously existing indexes, it will have to remove the entry from your new index, which might not have even been used. Before you run your command you could check in the Enterprise Manager to see how many processes have locks on the table you are using to see if perhaps there is just a lot of locking going on which the delete will have to wait and honor or something. Or you can open a second query analyzer window before you start the command, and issue the command "select * from master..sysprocesses where blocked > 0", then you can periodically execute that query while your command is running to see if it is being blocked and who is blocking it.

]]>