SQL Server 2008 big INSERT 在事务中包装后仍然很慢

Question

我一直在我的查询中查看一个很大的 INSERT 问题:

INSERT table_name
SELECT .....;

该表没有索引，需要大约 2000 万行才能插入其中。我在我们的一台服务器上的 SQL Server 2008 R2 中运行查询。原演出时长约40分钟。然后我在这里阅读帖子告诉我包装 INSERT在 BEGIN TRANSACTION/ COMMIT .我这样做了，花费的时间减少到 6 分钟。

但是，当我尝试在接下来的几次运行事务包装查询时，时间又回到了 40 分钟，就像 TRANSACTION 一样。效果没了。我不知道在接下来的运行中发生了什么。任何的想法？

添加:

另一个 post说 TRANSACTION 旨在用于数据一致性而不是性能，建议每 5K 行批量插入。如何将单个 INSERT SELECT 语句拆分为批处理？我很迷惑。

更新:

事实上，我发现性能改进不是来自 TRANSACTION，而是来自服务器端表缓存，因为我接下来运行了几次，性能大约是 5 分钟。

Answer 1

当你包裹很多 INSERT ... VALUES事务中的语句，可能会大幅提高速度，因为您不必在每次插入后将脏数据页写入磁盘。但是，当您包装单个 INSERT ... SELECT 时在显式事务中，没有加速，因为甚至在之前也存在隐式事务并且机制并没有真正改变。您的环境中很可能同时发生了其他变化。

性能逐渐下降可能是由于目标表增长或数据库增长导致的。前者永远不会停止增长，后者可能会随着您的数据库不断增长而变得更加可变/不可预测，因此它可能不是下降，而是一种趋势。

如果您始终可以确保将数据插入到空表中，请考虑更激进并每次都删除它。使用 SELECT INTO而不是 INSERT ... SELECT .这可能会也可能不会满足您的参照完整性需求。不同语法的优点是不同的日志记录策略。

如果在下一次插入之前无法删除该表，但可以确保在 INSERT 期间不会被其他连接访问。操作，您可以使用隔离级别或表提示来消除锁定；然而，实现类似目标的更安全的方法是 TABLOCK暗示。通过在开始时锁定整个表，这种提示有点相反。其他所有人都被排除在外，没有时间花在行级锁定上。

插入按目标表的(聚集的)主键排序的数据。您可以考虑在 INSERT 期间暂时禁用其他索引。 ，但不要掉以轻心，因为这只是另一种严重损害任何并发流量(如果存在)的方式。

注意您的 mdf 文件大小。避免看到它以小增量自动增长的情况。

最后的手段:做一些硬件利用率计划并对目标表进行分区。为此，您需要从“请更快”的心态转变为“我需要达到这个速度”的心态。这维护起来要复杂得多。