对具有许多索引的表进行慢速批量插入
我尝试将数百万条记录插入具有 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
相关文章