如何在 500,000+ 行表中不使用 Truncate Table 的情况下有效地删除行 [英] How to efficiently delete rows while NOT using Truncate Table in a 500,000+ rows table

问题描述

假设我们有一个包含 30 列和 500,000 行的 Sales 表.我想删除表中的 400,000(那些"toDelete='1'").

Let's say we have table Sales with 30 columns and 500,000 rows. I would like to delete 400,000 in the table (those where "toDelete='1'").

但我有一些限制:

• 经常"读取/写入表，我不希望长时间的删除"花费很长时间并锁定表太长时间
• 我需要跳过事务日志(例如使用 TRUNCATE)，但在执行 "DELETE ... WHERE..." 时(我需要放置一个条件)，但还没有找到任何方法来做到这一点...

• the table is read / written "often" and I would not like a long "delete" to take a long time and lock the table for too long
• I need to skip the transaction log (like with a TRUNCATE) but while doing a "DELETE ... WHERE..." (I need to put a condition), but haven't found any way to do this...

欢迎任何建议来转换一个

Any advice would be welcome to transform a

DELETE FROM Sales WHERE toDelete='1'

到更多分区的东西&可能没有事务日志.

to something more partitioned & possibly transaction log free.

推荐答案

调用 DELETE FROM TableName 将在一个大事务中完成整个删除.这很贵.

Calling DELETE FROM TableName will do the entire delete in one large transaction. This is expensive.

这是另一个选项，可以批量删除行:

Here is another option which will delete rows in batches :

deleteMore:
DELETE TOP(10000) Sales WHERE toDelete='1'
IF @@ROWCOUNT != 0
    goto deleteMore

这篇关于如何在 500,000+ 行表中不使用 Truncate Table 的情况下有效地删除行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！