sql - 使用相关子查询删除 4200 万行表的 SQL？

Question

我有一张 table cats 42,795,120 行。

显然这是很多行。所以当我这样做时:

/* owner_cats is a many-to-many join table */
DELETE FROM cats
WHERE cats.id_cat IN (
SELECT owner_cats.id_cat FROM owner_cats
WHERE owner_cats.id_owner = 1)

查询超时:(

(编辑:我需要增加我的 CommandTimeout 值，默认值只有 30 秒)

我不能用 TRUNCATE TABLE cats因为我不想吹走其他主人的猫。

我正在使用 SQL Server 2005，并将“恢复模型”设置为“简单”。

所以，我想过做这样的事情(顺便说一下，从应用程序执行这个 SQL):

DELETE TOP (25) PERCENT FROM cats
WHERE cats.id_cat IN (
SELECT owner_cats.id_cat FROM owner_cats
WHERE owner_cats.id_owner = 1)

DELETE TOP(50) PERCENT FROM cats
WHERE cats.id_cat IN (
SELECT owner_cats.id_cat FROM owner_cats
WHERE owner_cats.id_owner = 1)

DELETE FROM cats
WHERE cats.id_cat IN (
SELECT owner_cats.id_cat FROM owner_cats
WHERE owner_cats.id_owner = 1)

我的问题是:我可以的行数阈值是多少DELETE在 SQL Server 2005 中？

或者，如果我的方法不是最佳的，请提出更好的方法。谢谢。

这篇文章对我的帮助还不够:

SQL Server Efficiently dropping a group of rows with millions and millions of rows

编辑 (8/6/2010):

好的，我再次阅读上述链接后才意识到这些表上没有索引。此外，你们中的一些人已经在下面的评论中指出了这个问题。请记住，这是一个虚构的模式，所以即使 id_cat不是PK，因为在我的现实生活模式中，它不是一个独特的领域。

我将把索引放在:

cats.id_cat

owner_cats.id_cat

owner_cats.id_owner

我想我仍然掌握了这个数据仓库的窍门，显然我需要所有 JOIN 的索引。字段对吗？

但是，我需要花费数小时才能完成此批量加载过程。我已经在做 SqlBulkCopy (以块为单位，而不是一次全部为 4200 万个)。我有一些索引和 PK。我阅读了以下帖子，这些帖子证实了我的理论，即即使是批量复制，索引也会减慢速度:

SqlBulkCopy slow as molasses

What’s the fastest way to bulk insert a lot of data in SQL Server (C# client)

所以我要去 DROP我在复制之前的索引然后重新 CREATE他们完成后。

由于加载时间很长，我需要一段时间来测试这些建议。我会回来报告结果。

更新 (8/7/2010):

汤姆建议:

DELETE
FROM cats c
WHERE EXISTS (SELECT 1
FROM owner_cats o
WHERE o.id_cat = c.id_cat
AND o.id_owner = 1)

仍然没有索引，对于 4200 万行，使用上述方式需要 13:21 分:秒与 22:08。然而，对于 1300 万行，他以 2:13 和我以前的 2:10 的方式进行。这是一个好主意，但我仍然需要使用索引!

更新 (8/8/2010):

有什么不对劲的!现在有了索引，我上面的第一个删除查询花了 1:9 hrs:min (是的一个小时!) 4200 万行和 1300 万行分别是 22:08 分:秒和 13:21 分:秒 vs 2:10 分:秒。我现在要尝试使用索引进行 Tom 的查询，但这是朝着错误的方向前进。请帮忙。

更新 (8/9/2010):

Tom 的删除时间为 1:06 hrs:min(4200 万行)和 10:50 min:sec(1300 万行索引)，分别为 13:21 min:sec 和 2:13 min:sec。 当我使用索引一个数量级时，删除在我的数据库上花费的时间更长! 我想我知道为什么，我的数据库 .mdf 和 .ldf 在第一次(4200 万)删除期间从 3.5 GB 增长到 40.6 GB! 我究竟做错了什么？

更新 (8/10/2010):

由于缺乏任何其他选择，我想出了一个我觉得乏味的解决方案(希望是暂时的):

将数据库连接的超时时间增加到 1 小时(CommandTimeout=60000; 默认为 30 秒)

使用 Tom 的查询:DELETE FROM WHERE EXISTS (SELECT 1 ...)因为它的执行速度要快一点

DROP运行删除语句之前的所有索引和 PK (???)

运行 DELETE声明

CREATE所有索引和 PK

看起来很疯狂，但至少比使用 TRUNCATE 快并从第一个 owner_id 开始重新开始我的负载，因为我的一个 owner_id加载需要 2:30 小时:分钟，而我刚刚用 4200 万行描述的删除过程需要 17:22 分钟:秒。 (注意:如果我的加载过程抛出异常，我会重新开始处理 owner_id ，但我不想吹走之前的 owner_id ，所以我不想 TRUNCATE owner_cats 表，这就是我尝试使用 DELETE 的原因。)

任何帮助仍将不胜感激:)

Answer 1

没有实际的阈值。这取决于您的连接上的命令超时设置。

请记住，删除所有这些行所需的时间取决于:

查找感兴趣的行所需的时间

在事务日志中记录事务所需的时间

删除感兴趣的索引条目所需的时间

删除感兴趣的实际行所需的时间

等待其他进程停止使用该表所需的时间，以便您可以获得在这种情况下最有可能是排他表锁的时间

最后一点往往是最重要的。在另一个查询窗口中执行 sp_who2 命令以确保没有发生锁争用，从而阻止您的命令执行。

不正确配置的 SQL Server 在这种类型的查询中表现不佳。当处理大行时，事务日志太小和/或与数据文件共享相同的磁盘通常会导致严重的性能损失。

至于解决方案，嗯，就像所有事情一样，这取决于。这是您打算经常做的事情吗？根据您剩余的行数，最快的方法可能是将表重建为另一个名称，然后重命名它并重新创建其约束，所有这些都在事务中进行。如果这只是一个临时的事情，请确保您的 ADO CommandTimeout 设置得足够高，并且您可以承担这次大删除的费用。