改变100GB表格的有效方法

 

我们有许多数据库可以在其中一个表中存储10到100 GB的数据。 它包含图像数据。 问题是很多这些数据库是不正确地创建的。 基本上主键实际上并不是主键。 在可空列上创建了唯一索引。 其中一些有一个int作为主键而不是bigint。

所以我们一直在慢慢地修复这些数据库。 他们通过SQL Server 2008在SQL Server 2000上运行，尽管大多数主键问题都在SQL Server 2000上。问题是，我们不希望在转换表时锁定数据库一整天。 我们已经经历了几种策略：

  • 告诉SQL Server直接更改列类型。 这会锁定表格，直到完成为止，并且在很多情况下在隔夜后仍然没有完成。

  • 将所有图像一次性插入新表中。 这更容易被打断，但整个表格基本上都被写入了该过程中的日志文件。

  • 一次插入100行，其中行不存在于目标表中。 好处是他们可以在数据库继续使用时（性能受到很大影响），并且可以在任何时候任意停止和重新启动它，并且它可以防止100GB +日志文件。 这就是我们目前所做的，但是当目标表变得越来越大时，查找不存在的前100行非常慢。 UPDATE STATISTICS和DBCC INDEXDEFRAG有相当大的帮助，但在最近的尝试中，我们达到了这个程度，即使一次有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％的工作完成，我们总是可以回头修改任何差异。

    连接是问题。 不要这样做。 只需使用当前的聚集索引，使用合理的时间间隔循环访问当前表。 就像是： 

    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

    请注意，为获得最佳速度，请从目标表中除去所有索引（包括聚簇索引），然后在插入所有行后添加索引。

    编辑 ：改变范围间隔以防止重叠（因为BETWEEN包括端点）

    最后一个澄清：我的示例脚本的总体观点是，您只是想按照合理顺序浏览当前记录，并将它们全部批量放入新表中。 没有理由每次都继续检查目标表，因为你应该已经知道你在那里放了什么，还有什么还在。 大多数情况下，使用聚簇索引（如果有的话）是有意义的，因为这意味着它可以遍历表的物理顺序而不用做书签查找。 如果表没有聚类，那么就使用最有意义的东西（可能是你的PK）。

    链接地址: http://www.djcxy.com/p/2809.html

    上一篇: Efficient way to alter 100GB table

    下一篇: JS/overflow:hidden performance issues in Safari on iPad/iOS