Optimización de Consultas de Actualización de SQL Server para Grandes Volúmenes de Datos
La actualización de tablas muy grandes puede ser una tarea que requiere tiempo y, a veces, puede tardar horas en completarse. Además de esto, también podría causar problemas de bloqueo.
Estos son algunos consejos para optimizar las actualizaciones de SQL Server en grandes volúmenes de datos.
- Eliminar el índice de la columna a actualizar.
- Ejecutar la actualización en lotes más pequeños.
- Desactivación de desencadenadores de eliminación.
- Sustitución de la instrucción de actualización por una operación de inserción masiva.
Dicho esto, apliquemos 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 ENDGOCREATE INDEX ix_col1 ON tblverylargetable(col1) GO CREATE INDEX ix_col2_col3 ON tblverylargetable(col2) INCLUDE(col3)
Considere la siguiente consulta de actualización que debe optimizarse. Es una consulta muy sencilla para actualizar una sola columna.
UPDATE tblverylargetable SET col1 = 'D'WHERE col1 = 'Dummy'
La consulta tarda 2:19 minutos en ejecutarse.
Veamos el plan de ejecución de la consulta que se muestra a continuación. Además de la actualización del índice agrupado, también se actualiza el índice ix_col1. La operación de actualización de índice y clasificación juntas toma el 64% del costo de ejecución.
1. Eliminar el índice de la columna a actualizar
La misma consulta tarda de 14 a 18 segundos cuando no hay ningún índice en col1. Por lo tanto, una consulta de actualización se ejecuta más rápido si la columna que se va a actualizar no es una columna de clave de índice. El índice siempre se puede crear una vez que se complete la actualización.
2. Ejecución de la actualización en lotes más pequeños
La consulta se puede optimizar aún más ejecutándola en lotes más pequeños. Esto es generalmente más rápido. El siguiente código actualiza los registros en lotes de 20000.
DECLARE @i INT=1 WHILE( @i <= 10 ) BEGIN UPDATE TOP(20000) tblverylargetable SET col1 = 'D' WHERE col1 = 'Dummy' SET @i=@i + 1 END
La consulta anterior tarda de 6 a 8 segundos en ejecutarse. Al actualizar por lotes, incluso si la actualización falla o necesita detenerse, solo se revertirán las filas del lote actual.
3. Deshabilitar disparadores de eliminación
Los disparadores con cursores pueden ralentizar enormemente el rendimiento de una consulta de eliminación. Deshabilitar los disparadores después de eliminar aumentará considerablemente el rendimiento de la consulta.
4. Reemplazar la instrucción de actualización con una operación de inserción masiva
Una instrucción de actualización es una operación totalmente registrada y, por lo tanto, sin duda llevará una cantidad considerable de tiempo si se van a actualizar millones de filas.La forma más rápida de acelerar la consulta de actualización es reemplazarla con una operación de inserción masiva. Es una operación de registro mínimo en un modelo de recuperación simple y de registro masivo. Esto se puede hacer fácilmente haciendo una inserción masiva en una tabla nueva y luego cambiar el nombre de la tabla a la original. Los índices y restricciones requeridos se pueden crear en una tabla nueva según sea necesario.
El siguiente código muestra cómo se puede convertir la actualización en una operación de inserción masiva. Se tarda 4 segundos en ejecutarse.
SELECT sno, CASE col1 WHEN 'Dummy' THEN 'D' ELSE col1 END AS col1, col2, col3 INTO tblverylargetabletemp FROM tblverylargetable
El inserto a granel se puede optimizar aún más para obtener un aumento adicional del rendimiento.
Espero que esto ayude!!!Facebook
Síguenos en Facebook | Síguenos en Twitter / Únete al grupo de SQL Server de más rápido crecimiento en FaceBook