SQL中的Delete语句非常慢 [英] Delete statement in SQL is very slow

问题描述

我有这样的语句正在超时：

I have statements like this that are timing out:

DELETE FROM [table] WHERE [COL] IN ( '1', '2', '6', '12', '24', '7', '3', '5')

我尝试一次执行以下操作：

I tried doing one at a time like this:

DELETE FROM [table] WHERE [COL] IN ( '1' )

到现在为止仍是22分钟。

and so far it's at 22 minutes and still going.

表中有260,000行，为四列。

The table has 260,000 rows in it and is four columns.

有人对为什么这样做有任何想法吗？这么慢，如何加快速度？
我在[WOL]上确实有一个非唯一，非聚集的索引，我正在其中进行WHERE操作。
我正在使用SQL Server 2008 R2

Does anyone have any ideas why this would be so slow and how to speed it up? I do have a non-unique, non-clustered index on the [COL] that i'm doing the WHERE on. I'm using SQL Server 2008 R2

更新：我在表上没有触发器。

update: I have no triggers on the table.

推荐答案

可能导致删除速度变慢的事情：

Things that can cause a delete to be slow:

• 删除大量记录


• 许多索引


• 在子表中缺少外键索引。 （感谢@CesarAlvaradoDiaz在评论中提到此问题）


• 死锁和阻止


• 触发


• 级联删除（要删除的十条父记录可能意味着
  百万条子记录被删除）


• 需要增长的事务日志


• 许多外键可以检查

• deleting a lot of records
• many indexes
• missing indexes on foreign keys in child tables. (thank you to @CesarAlvaradoDiaz for mentioning this in the comments)
• deadlocks and blocking
• triggers
• cascade delete (those ten parent records you are deleting could mean millions of child records getting deleted)
• Transaction log needing to grow
• Many Foreign keys to check

因此，您的选择是找出正在阻止的内容并进行修复，或者在非工作时间运行删除操作不会干扰正常的生产负荷。您可以批量运行删除（如果您有触发器，级联删除或大量记录，则很有用）。您可以删除并重新创建索引（最好也可以在非工作时间完成）。

So your choices are to find out what is blocking and fix it or run the deletes in off hours when they won't be interfering with the normal production load. You can run the delete in batches (useful if you have triggers, cascade delete, or a large number of records). You can drop and recreate the indexes (best if you can do that in off hours too).

这篇关于SQL中的Delete语句非常慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！