代码之家 › 专栏 › 技术社区 › Bryce Wagner

改变100GB表的有效方法

bulkinsert sql-server-2000

7

Bryce Wagner · 技术社区 · 14 年前

所以我们一直在慢慢检查和修复这些数据库。它们在SQLServer2000到SQLServer2008上运行,尽管大多数有主键问题的都在SQLServer2000上。问题是,我们不想在数据库转换表时锁定它一整天。我们经历了几个策略:

告诉SQL Server直接更改列类型。这会将表锁定,直到它完成为止,在许多情况下,在离开它一夜之后,它仍然没有完成。
一次性将所有图像插入新表。这更容易被中断,但整个表基本上会在这个过程中写入日志文件。
在目标表中不存在行的位置一次插入100行。好处是,他们可以继续使用数据库,而这正在进行(与一个大的性能打击),它可以停止和重新启动任意在任何时候,它防止了100GB+日志文件。这就是我们目前正在做的,但是随着目标表越来越大,查找不存在的前100行的速度会变得非常慢。updatestatistics和dbccindexdefrag有相当大的帮助,但是在最近的尝试中,我们发现即使一次有100个图像也没有响应。
```
INSERT INTO %s  
  SELECT TOP 100 Source.*  
  FROM %s AS Source WITH (NOLOCK)  
  LEFT OUTER JOIN %s AS Target WITH (NOLOCK) ON Source.DocumentID = Target.DocumentID  
  WHERE Target.DocumentID IS NULL  
  ORDER BY Source.DocumentID  
```

所以问题是,有没有一种方法可以以高效且可恢复的方式复制大量数据?它不一定要100%准确,我们总是可以回去,并在最后解决任何差异,只要它做99%的工作。

1 回复 | 直到 14 年前

1

3

BradC 14 年前

现在的 聚集索引。比如:

Declare @idrange int;
Set @idrange = 1;

WHILE @idrange < 10000000

INSERT INTO Destination
  SELECT *  
  FROM Source
  WHERE DocumentID between @idrange and @idrange + 999
  ORDER BY Source.DocumentID  

Set @idrange = @idrange + 1000
End

请注意,为了获得最佳速度,请从目标表中删除所有索引(包括聚集索引),然后在插入所有行后添加索引。

编辑 :更改了范围间隔以防止重叠(因为其间包括端点)

最后一点澄清:我的示例脚本的总体要点是,您只需以合理的顺序遍历当前记录,并将它们全部成批放入新表中。没有理由每次都继续检查destination表,因为您应该已经知道您在那里放了什么,还剩下什么。大多数情况下,使用聚集索引(如果有)是有意义的,因为这意味着它可以遍历表的物理顺序,而无需进行书签查找。如果表没有集群,那么就使用最有意义的方法(可能是您的PK)。