Slow bulk insert for table with many indexes

sql sql-server indexing bulkinsert
Ole Lynge picture Ole Lynge · Apr 15, 2009 · Viewed 36.5k times · Source

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

Lucero picture Lucero · Apr 15, 2009

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

Related questions

Creating an index on a table variable

Can you create an index on a table variable in SQL Server 2000? i.e. DECLARE @TEMPTABLE TABLE ( [ID] [int] NOT NULL PRIMARY KEY ,[Name] [nvarchar] (255) COLLATE DATABASE_DEFAULT NULL ) Can I create an index on Name?

Read More
Rebuild all indexes in a Database

I have a very large SQL Server 2008 R2 database (1.5TB) and will be copying some data from column to column within the same table. I've been told that the schema has a large number of indexes and was wondering if …

Read More
sql primary key and index

Say I have an ID row (int) in a database set as the primary key. If I query off the ID often do I also need to index it? Or does it being a primary key mean it's already indexed? …

Read More