对具有许多索引的表进行慢速批量插入 [英] Slow bulk insert for table with many indexes

问题描述

我尝试将数百万条记录插入具有 20 多个索引的表中.

I try to insert millions of records into a table that has more than 20 indexes.

在上次运行中，每 100.000 行花费了超过 4 小时，查询在 3½ 天后被取消...

In the last run it took more than 4 hours per 100.000 rows, and the query was cancelled after 3½ days...

您对如何加快速度有什么建议吗.

Do you have any suggestions about how to speed this up.

(我怀疑是因为索引太多.如果你也这么认为，我如何在操作前自动删除索引，然后再次创建相同的索引?)

(I suspect the many indexes to be the cause. If you also think so, how can I automatically drop indexes before the operation, and then create the same indexes afterwards again?)

额外信息:

• 索引使用的空间大约是数据单独使用空间的 4 倍
• 插入被包装在每 100.000 行的事务中.

状态更新:

接受的答案帮助我加快了速度.

The accepted answer helped me make it much faster.

推荐答案

您可以禁用和启用索引.请注意，禁用它们可能会产生不需要的副作用(例如具有重复的主键或唯一索引等)，只有在重新启用索引时才会发现这些副作用.

You can disable and enable the indexes. Note that disabling them can have unwanted side-effects (such as having duplicate primary keys or unique indices etc.) which will only be found when re-enabling the indexes.

--Disable Index
ALTER INDEX [IXYourIndex] ON YourTable DISABLE
GO

--Enable Index
ALTER INDEX [IXYourIndex] ON YourTable REBUILD
GO

这篇关于对具有许多索引的表进行慢速批量插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！