sql - Slow bulk insert for table with many indexes

Question

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

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?)

Extra info:

• The space used by the indexes is about 4 times the space used by the data alone
• The inserts are wrapped in a transaction per 100.000 rows.

---

Update on status:

The accepted answer helped me make it much faster.

Answer 1

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