☰

如何加快在 SQL Server 中向大型表添加列的速度

tag-icon • tag-icon sql-server
如何加快在 SQL Server 中向大型表添加列的速度

我想向一个包含约 1000 万行的 SQL Server 表添加一列。我认为这个查询会最终完成添加我想要的列：

alter table T
add mycol bit not null default 0

但它已经持续了几个小时。有没有捷径可以将“非空默认 0”列插入到大型表中？或者这本身就很慢？

这是 Sql Server 2000。稍后我必须在 Sql Server 2008 上做类似的事情。

答案1

根据您的行大小、表大小、索引等，我看到 SQL Server 2000 需要磨合几个小时（4-5 个小时）才最终完成。

你现在能做的最糟糕的事情就是“惊慌失措”并强行杀死它。让它自己跑出去。

将来，您可能希望尝试按照 Farseeker 所提到的操作，创建第二个（空）结构并以此方式复制您的记录。

  • 表格行越长，花费的时间越长。
  • 该表上的索引越多，所需的时间就越长。
  • 如果您添加默认值（您已添加），则需要更长时间。
  • 如果服务器使用量很大，则需要更长时间。
  • 如果您不锁定该数据库或将其置于单用户模式，则需要更长时间。

当我不得不做这种丑陋的事情时，我会尝试在晚上做...比如凌晨 2 点，那时没人在（并且服务器没有进行维护）。

祝你好运！ ：-）

答案2

嗯，1000 万行是相当多的，但它并不超出 MSSQL 的范围，而且似乎非常慢。

我们有一张表，行数非常大（设计不佳），超过 1000 万行。当我们必须修改结构时，速度确实非常慢，所以我们做的是（让表保持在线，这是记忆中的粗略记录，因为那是很久以前的事了）：

  • 创建了带有后缀“C”（表示转换）和新结构（即与旧结构相同，但具有新的列/索引/等）的新表
  • SELECT * INTO 表 C FROM 表
  • sp_rename ‘表’ ‘表旧’
  • sp_rename ‘表 C’ ‘表’

这样，转换需要多长时间就无关紧要了，因为旧数据是在线的。不过，在转换过程中，这可能会导致行写入表时出现问题（这对我们来说不是问题，因为数据每天只写入一次，但每小时查询数千次），所以您可能需要调查一下。

答案3

您可以尝试在单独的批处理中执行操作的每个步骤，例如

alter table T add mycol bit null
go
update T set mycol = 0
go
alter table T alter column mycol bit not null
go
alter table T add default 0 for mycol
go

优点是：

  • 您可以获得有关操作进度的更好的反馈，因为现在有 4 个单独的批次，每个批次大约需要 1/4 的时间。
  • 它降低了从客户端代码运行时出现超时错误的可能性。
  • 我发现它有时可以提高性能。

您还可以尝试在进行更改之前删除表上的所有非聚集索引，然后恢复它们。添加列可能会涉及大规模页面拆分或其他低级重新排列，并且您可以在此过程中避免更新非聚集索引的开销。

答案4

我曾经在至少有 6500 万行的表中做过类似的事情，而且并没有花那么长时间。你的磁盘系统是否有足够的内存和足够的性能

如果您想加快该过程，您可以在更改表之前删除除聚集索引和外键约束之外的所有索引，但这必须在系统未使用时进行，否则您可能会得到不一致的数据。但最终您需要在完成之前应用外键和索引，但这样可以减轻事务日志的痛苦，至少如果您在简单恢复模式下运行的话。在 SQL Server 2008 中，您可以使用 ONLINE=on 和 SORT_IN_TEMPDB=on 构建索引

哈坎·温瑟

相关内容