改进 INSERT INTO - FROM SELECT，SQL 查询

Question

目前，我通过编程（使用C#）生成了以下类型的查询。

INSERT INTO TableName (Field1, Field2, Field3)
SELECT Field1, Field2, Field3 FROM TableName2

问题在于SELECT可能会有很多记录（比如一百万条），所以需要很长时间，并且结果是连接超时。

另外，如果我将所有的插入操作分开成单个插入（例如，一百万个插入查询），执行时间会很长...但是可以工作...

有没有办法改进这种类型的查询？

我使用的是MSSQL 2005

谢谢

- Melursus

Answer 1

我发现，如果你有很多按顺序执行的INSERT语句，你可以在每个xxxx数量的插入语句后添加一个'GO'语句来提高性能：

...
INSERT INTO Table ( ... ) VALUES ( ... )
INSERT INTO Table ( ... ) VALUES ( ... )
INSERT INTO Table ( ... ) VALUES ( ... )
GO
INSERT INTO Table ( ... ) VALUES ( ... )
INSERT INTO Table ( ... ) VALUES ( ... )
...

另一种可能性是确保您的INSERT INTO .. SELECT FROM查询不会一次性插入所有内容，而是使用某种分页技术：

INSERT INTO Table ...
SELECT ...
FROM OtherTable WHERE Id > x and Id < y

- Frederik Gheysels

Answer 2

如果是完整的复制，您是否应该考虑使用批量加载工具呢？

• BULK INSERT (TSQL)
• SqlBulkCopy (.NET)
• bcp (命令行)
• 等等

如果您有Where子句，我建议您检查它是否适当地建有索引...

此外：

• 在执行INSERT操作之前，或许可以删除索引和触发器（稍后重新创建）
• 考虑删除整个表格并使用SELECT INTO？（请参见评论）

- Marc Gravell

---

不要使用select into...，因为该查询会锁定系统表，直到表在同一事务中创建完成。而且这样做也不会带来任何性能提升。 - Brian Rudolph

Answer 3

好的，这里有几个基本问题。

1. I/O - 如果表不在不同的磁盘上，从一个表读取数据并插入到另一个表中，很可能会导致磁盘争用。将相对的表放在物理上不同的磁盘上。

2. 事务日志 - 你需要确保你的事务日志在它自己的磁盘上，或者使用较小的事务（每次几千行）或使用未记录的BCP\批量插入。

3. 聚集索引 - 如果你将所有这些行插入到目标表中，并且它的聚集索引（数据写入磁盘的物理顺序）不是按顺序写入的，由于页面分裂和重新分配，磁盘IO需求会飙升。一个简单的解决方法是在接收表上创建一个聚集索引，该索引是一个连续的种子键。这通常会确保你获得连续的写入到表中，几乎总是在最后。

4. 文件扩展 - 确保将SQL设置为以相当快的速度扩展其文件，例如10%左右。否则，它将不得不不断调整其文件大小并清零磁盘。还有一些方法可以防止它必须清零磁盘，例如为Sql Service用户启用组策略中的批量文件操作权限。

说实话，除了其他建议之外，如果你要在一个事务中插入数百万行数据，很可能不会真正快速。如果使用批量插入方式，速度会大大提高，但这可能并不是从应用程序角度所需要的。

- Brian Rudolph

Answer 4

将您正在使用的SqlCommand的CommandTimeout属性设置为合理值（例如10分钟）。请记住，CommandTimeout的单位是秒。

- Anton Gogolev

Answer 5

这里有一些不错的答案。

我想补充一点，如果目标表上有索引，它们会减慢操作速度。然而，如果你使用删除创建技术，重建索引有时可能需要很长时间。

如果你不想删除索引，在你的 SELECT 中使用一个与目标聚集索引匹配的 ORDER BY，这似乎有所帮助（可能有助于最小化页面分裂）。

- Cade Roux

Answer 6

您没有说明您使用这种方法解决的问题。显然，WHERE子句会缩小记录集。但是，如果结果集不会在新表中被修改，那么为什么要复制数据呢？为什么不直接从源查询呢？

- SAMills

Answer 7

可以通过使用文件进行批量加载，然后使用bcp/BULK INSERT进行操作，或者将其分批处理为每批5K左右。

- SQLMenace

Answer 8

首先，永远不要尝试通过C#插入一百万条记录。永远不要逐个处理大量的记录。这是应该由数据库完成的工作。使用批量插入、SSIS或DTS来完成此操作。然后在非工作时间安排它作为一个任务。如果仍然需要太长时间，那么我建议您分批运行几千条记录（您将不得不根据自己的数据库进行调整，以确定最佳选择，因为您可以安全处理的数量取决于表格、索引、服务器速度以及有多少用户也在尝试对相同的表格进行操作）。

- HLGEM

---

而且，他正在使用数据库。C#代码只是启动查询。 - Joel Coehoorn

Answer 9

我们过去使用的另一种方法是创建一个带有我们想要移动的主键的临时表，并使用while循环。这样，您可以以块状方式执行操作，从而避免了大型事务开销，如果取消操作，则必须回滚。

基本上，您最终要做的是将数据插入到目标表中，例如：insert into tablename (...) select (...) from table name where primary key in (select top 10000 key from temptable)

您需要在第二个结果集中获取前10000条记录，以便从临时表中删除它们，以免再次处理。

另一种方法是使用游标来减少每次处理的记录数。

另一种循环方法是在while循环中执行以下操作：

declare @stop as int set @stop = (select count(primaryKey) from tableName where primaryKey not in destinstiontable)

while (@stop > 0) begin transaction

insert into destinationTable (...) select (...) from sourcetable where primaryKey not in (select primarykey from destinationtable)

commit

set @stop = (select count(primaryKey) from tableName where primaryKey not in destinstiontable) end

这不是最有效的方法，但它可以工作，并且应该允许您保持事务日志。除非您需要它，还要确保使用no lock关键字，以便在执行此大型移动时不会阻止其他事务（除非您使用BCP或DTS，因为它们要快得多）。

其中一些可能是您最好的选择。使用BCP、DTS或其他批量工具。如果您可以删除索引，将使事情变得更快。

- Joshua Cauble

---

仅使用键创建临时表将占用 tempdb 的资源，只会使 IO 问题更加严重。 - Brian Rudolph

确实是这样。在这种情况下，我仍然更喜欢使用DTS或其他BCP工具。试图手动完成可能会很痛苦和资源密集。 - Joshua Cauble

Answer 10

你是否通过SQL Server管理工具测试过SQL语句的实际执行时间？建议从那里开始。 你可以优化select语句的性能。 同时，在插入数据的目标表上使用tablock提示或许能够提高性能。

- Will Rickards