Inserción masiva lenta para la tabla con muchos índices

Intento insert millones de loggings en una tabla que tiene más de 20 índices.

En la última ejecución, tomó más de 4 horas por cada 100.000 filas, y la consulta se canceló después de 3½ días …

¿Tiene alguna sugerencia sobre cómo acelerar esto?

(Sospecho que los muchos índices son la causa. Si también lo crees, ¿cómo puedo soltar automáticamente los índices antes de la operación, y luego crear los mismos índices más adelante?)

Información extra:

  • El espacio utilizado por los índices es aproximadamente 4 veces el espacio utilizado solo por los datos
  • Los insertos están envueltos en una transacción por cada 100.000 filas.

Actualización sobre el estado:

La respuesta aceptada me ayudó a hacerlo mucho más rápido.

Puede deshabilitar y habilitar los índices. Tenga en count que deshabilitarlos puede tener efectos secundarios no deseados (como tener keys primarias duplicadas o índices únicos, etc.) que solo se encontrarán al volver a habilitar los índices.

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

Esto suena como una operación de depósito de datos. Sería normal soltar los índices antes del inserto y rebuildlos después.

Cuando reconstruya los índices, primero cree el índice agrupado y, a la inversa, colóquelo último. Todos deberían tener fillfactor 100%.

El código debería ser algo como esto

 if object_id('Index') is not null drop table IndexList select name into Index from dbo.sysindexes where id = object_id('Fact') if exists (select name from Index where name = 'id1') drop index Fact.id1 if exists (select name from Index where name = 'id2') drop index Fact.id2 if exists (select name from Index where name = 'id3') drop index Fact.id3 . . BIG INSERT RECREATE THE INDEXES 

Como se señala en otra respuesta, los índices de inhabilitación serán un muy buen comienzo.

4 horas por 100.000 filas […] Las inserciones se envuelven en una transacción por cada 100.000 filas.

Debería considerar networkingucir el número, el server tiene que mantener una gran cantidad de estado mientras está en una transacción (por lo que se puede retrotraer), esto (junto con los índices) significa que agregar datos es un trabajo muy difícil.

¿Por qué no ajustar cada instrucción de inserción en su propia transacción?

También observe la naturaleza del SQL que está utilizando, ¿está agregando una fila por statement (y networking de ida y vuelta), o agregando muchas?

Desactivar y luego volver a habilitar los índices se sugiere con frecuencia en esos casos. Sin embargo, tengo mis dudas sobre este enfoque, porque:

(1) El usuario de la database de la aplicación necesita privilegios de alteración del esquema, que normalmente no debería tener. (2) En primer lugar, el esquema de inserción y / o índice insertado podría ser menor que óptimo, de lo contrario la reconstrucción de treees de índice completos no debería ser más rápida que una inserción de lotes decente (por ejemplo, que el cliente emita una instrucción de inserción a la vez). miles de viajes de ida y vuelta del server, o una mala elección en el índice agrupado, que conduce a divisiones de nodos de índice constantes).

Es por eso que mis sugerencias parecen un poco diferentes:

  • Aumentar ADO.NET BatchSize
  • Elija el índice agrupado de la tabla de destino sabiamente, de modo que las inserciones no conduzcan a divisiones de nodos de índice agrupados. Por lo general, una columna de identidad es una buena opción
  • Deje que el cliente inserte primero en una tabla de almacenamiento dynamic temporal (las tablas de almacenamiento dynamic no tienen ningún índice agrupado); luego, emita una gran instrucción "insert-en-seleccionar" para insert todos los datos de la tabla de etapas en la tabla real de objectives
  • Aplicar SqlBulkCopy
  • Disminuya el logging de transactions eligiendo el model de recuperación de logging masivo

Puede encontrar información más detallada en este artículo .