删除具有4200万行的表的相关子查询的SQL？

显然这是很多行.所以当我这样做时：

/* 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猫,因为我不想吹掉其他主人的猫.

我正在使用SQL Server 2005,“恢复模式”设置为“简单”.

所以,我想做这样的事情(从应用程序btw执行这个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)

我的问题是：在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(在块中,一次不是42 mil).我有一些索引和PK.我阅读了以下帖子,这些帖子证实了我的理论,即即使是批量复制,索引也在减速：

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

因此,我将在复制之前删除索引,然后在完成后重新创建它们.

由于加载时间很长,我需要花一些时间来测试这些建议.我会用结果报告.

更新(2010年8月7日)：

汤姆建议：

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.这是一个很好的想法,但我仍然需要使用索引！

更新(2010年8月8日)：

有些事情是非常错误的！现在索引打开,我上面的第一个删除查询花了1：9小时：分钟(是一小时！)对比22:08分钟：秒和13分21秒：秒对比2：10分钟：秒为42密耳行和分别为13密耳行.我现在要用索引查询Tom的查询,但这是朝着错误的方向前进.请帮忙.

更新(2010年8月9日)：

汤姆的删除时间为1:06小时：分钟为42密耳行,10：50分钟：秒为13密耳行,索引分别为13:21分钟：秒和2分13分钟：秒.当我使用一个数量级的索引时,删除在我的数据库上花费的时间更长！我想我知道为什么,我的数据库.mdf和.ldf在第一次(42 mil)删除期间从3.5 GB增长到40.6 GB！我究竟做错了什么？

更新(8/10/2010)：

由于缺乏任何其他选择,我提出了我认为是一个平淡无奇的解决方案(希望是暂时的)：

>将数据库连接的超时时间增加到1小时(CommandTimeout = 60000;默认为30秒)
>使用Tom的查询：DELETE FROM WHERE EXISTS(SELECT 1 …)因为它执行得更快一点
>在运行delete语句之前删除所有索引和PK(???)
>运行DELETE语句
>创建所有索引和PK

看起来很疯狂,但至少它比使用TRUNCATE更快,并从第一个owner_id开始我的负载,因为我的owner_id之一需要2:30小时：加载最小值与17:22分钟：秒删除过程我刚刚用42密耳的行描述. (注意：如果我的加载进程抛出异常,我会重新开始那个owner_id,但我不想吹掉之前的owner_id,所以我不想TRUNCATE owner_cats表,这就是为什么我要尝试使用DELETE.)

不再需要帮助仍然会受到赞赏:)

解决方法

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

>找到感兴趣的行所需的时间
>在事务日志中记录事务所花费的时间
>删除感兴趣的索引条目所需的时间
>删除实际感兴趣行所需的时间
>等待其他进程停止使用表所需的时间,以便您可以获得在这种情况下最有可能是独占表锁的内容

最后一点可能通常是最重要的.在另一个查询窗口中执行sp_who2命令以确保不会发生锁争用,从而阻止执行命令.

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

至于解决方案,好吧,就像所有事情一样,这取决于.这是你打算经常做的吗？根据您剩余的行数,最快的方法可能是将表重建为另一个名称,然后在事务内重命名并重新创建约束.如果这只是一个特别的事情,请确保您的ADO CommandTimeout设置得足够高,您只需承担这次大删除的费用.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!