显然这是很多行.所以当我这样做时:
/* 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设置得足够高,您只需承担这次大删除的费用.