sql - upadte - Inserción masiva lenta para la tabla con muchos índices
para que sirven los indices en sql (4)
Intento insertar millones de registros 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.
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 reducir el número, el servidor 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 declaración (y red 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 base de datos 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 árboles de índice completos no debería ser más rápida que una inserción de lotes decente (por ejemplo, el cliente emitiendo una instrucción de inserción a la vez, causando miles de viajes de ida y vuelta del servidor, 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 dinámico temporal (las tablas de almacenamiento dinámico no tienen ningún índice agrupado); luego, emita una gran instrucción "insertar-en-seleccionar" para insertar todos los datos de la tabla de etapas en la tabla real de objetivos
- Aplicar SqlBulkCopy
- Disminuya el registro de transacciones eligiendo el modelo de recuperación de registro masivo
Puede encontrar información más detallada en este artículo .
Esto suena como una operación de depósito de datos. Sería normal soltar los índices antes del inserto y reconstruirlos después.
Cuando reconstruya los índices, primero cree el índice agrupado y, a la inversa, colóquelo en último lugar. 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
Puede deshabilitar y habilitar los índices. Tenga en cuenta que deshabilitarlos puede tener efectos secundarios no deseados (como tener claves 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