Actualizar tablas muy grandes puede llevar mucho tiempo y a veces puede tardar horas en terminar, producirse problemas de bloqueo. En ocasiones queremos modificar cientos de registros de una misma tabla y necesitamos que el proceso sea lo mas ligero posible…
Consejos para optimizar las actualizaciones en grandes volúmenes de datos:
1. Quitar índice en la columna que se actualiza. Después se volverán a recrear.
2. Ejecutar la actualización en lotes más pequeños.
3. Deshabilitar o quitar triggers.
4. Sustituir la instrucción Update con una operación de Bulk Insert.
Vamos a aplicar los puntos anteriores para optimizar una consulta de actualización.
El siguiente código crea una tabla ficticia con 200.000 filas e índices requeridos.
CREATE TABLE tblverylargetable
(
sno INT IDENTITY,
col1 CHAR(800),
col2 CHAR(800),
col3 CHAR(800)
)
GO
DECLARE @i INT=0
WHILE( @i < 200000 )
BEGIN
INSERT INTO tblverylargetable
VALUES ('Dummy',
Replicate('Dummy', 160),
Replicate('Dummy', 160))
SET @i=@i + 1
END
GO
CREATE INDEX ix_col1
ON tblverylargetable(col1)
GO
CREATE INDEX ix_col2_col3
ON tblverylargetable(col2)
INCLUDE(col3)
Considerar la siguiente consulta de actualización, que debe será optimizada. (Actualiza una sola columna).
UPDATE tblverylargetable
SET col1 = 'D'
WHERE col1 = 'Dummy'
Además de la actualización de índice agrupado, se actualiza el índice ix_col1.
Quitar el índice en la columna antes de actualizar. Una consulta de actualización se ejecuta más rápido si la columna actualizarse no es una columna índice. El índice se puede crear siempre una vez que finalice la actualización.
Drop index ix_col1 on tblverylargetable
La consulta se puede optimizar mediante la ejecución en lotes más pequeños. Esto es generalmente más rápido. El código siguiente actualiza los registros de lotes en rangos de 20000.
DECLARE @i INT=1
WHILE( @i <= 10 )
BEGIN
UPDATE TOP(20000) tblverylargetable
SET col1 = 'Dummy'
WHERE col1 = 'D'
SET @i=@i + 1
END
Deshabilitar o quitar triggers:
DISABLE TRIGGER NombreTrigger ON Nombretabla;
Usando cursores pueden bajar el rendimiento de una consulta de eliminación.
Después de desactivar o eliminar desencadenadores aumentará considerablemente el rendimiento de las consultas.
Sustituir la instrucción Update con una operación de Bulk Insert.
La forma más rápida para acelerar la consulta de actualización es hacerla con una operación de inserción masiva. Ya que también incide el modelo de recuperación del log de transacciones, si lo configuramos a simple, se registra mínimamente. En inserciones masivas, si es posible y la lógica de la aplicación lo permite, aconsejo eliminar los índices y restricciones antes de la inserción y crearlos Después.
SQL Server admite tres métodos de carga masiva:
1.- La inserción masiva como un comando de Transact-SQL, con la BULK INSERT de Transact-SQL.
2.- La utilidad BCP (La utilidad bcp hace copias masivas de los datos entre una instancia de Microsoft SQL Server y un archivo de datos)
3.- DTS (Servicio de transformación de datos)
Configurar el log de transacciones a SIMPLE o BULK-logger. Esto es una de las "Prácticas recomendadas para la carga masiva de datos", puede mejorar el rendimiento de la instrucción y reducir la posibilidad de que la operación rellene el espacio del registro de transacciones disponible durante la transacción.
Apunte y recopilación por Norman M. Pardell
Puedes consultarme, si deseas cualquier aclaración, pregunta o sugerencia en: Contacto, contestaré tan pronto como me sea posible.
