Optimizar o disminuir el tiempo de ejecución de un update

[EdisonRivas]

Buenas Tardes.

Mi situación es la siguiente, al ejecutar el siguiente update:

update m
set FechaMovimiento=I.Fecha_Proceso
from
Movimientos m inner join items i(nolock)
on m.id_contrato=i.id_contrato
and m.id_sobre=i.id_sobre
and m.id_item=i.id_item


Se demora mucho tiempo, de hecho ya va casi 2 días de ejecución y todavia no se termina.

Un dato importante es que las tablas que se estan uniendo tienen una gran cantidad de datos:

Movimientos:45,000.000 filas
Items: 60,000.000 filas

Me gustaria saber si existen formas, consejos o recomendaciones de como disminuir el tiempo al realizar el update.

Gracias de antemano por su ayuda y comentarios

[iislas]

Primiero, NO ES RECOMENDABLE utilizar el NOLOCK

Segundo, debería verse algo asi:

Código SQL:

UPDATE Movimientos 
 SET FechaMovimiento = I.Fecha_Proceso
 FROM
 Movimientos m INNER JOIN items i(nolock)
 ON m.id_contrato=i.id_contrato
 AND m.id_sobre=i.id_sobre
 AND m.id_item=i.id_item
 

Tercero: ¿Tienes INDICES en tus tablas?

Cuarto: Si vas a actualizar varios registros, te recomiendo que hagas un UPDATE controlado mediante SET ROWCOUNT xxx

Donde xxx, es el numero de registros a actualizar

[GeriReshef]

Cocuerdo con lo que que iislas ha escrito.
Yo añadiria otro filtro-
Where M.FechaMovimiento = I.Fecha_Proceso
para evitar inecesarias actualizaciones, especialmente si intentas la recomendación de iislas sobre el Set RowCount.
Otros factores que influyen el rendimiento de esta instrucción:
1. M.FechaMovimiento es parte del Clustered Index?
2. La tabla Movimientos tiene triggers?
3. Cuantas filas hay en Movimientos ?
4. Verifica que la clausula On (del Join) es correcta y no crea productos cartesianos.

[damimg]

Iniciado por iislas

Primiero, NO ES RECOMENDABLE utilizar el NOLOCK

Segundo, debería verse algo asi:

Código SQL:

UPDATE Movimientos 
 SET FechaMovimiento = I.Fecha_Proceso
 FROM
 Movimientos m INNER JOIN items i(nolock)
 ON m.id_contrato=i.id_contrato
 AND m.id_sobre=i.id_sobre
 AND m.id_item=i.id_item
 

Tercero: ¿Tienes INDICES en tus tablas?

Cuarto: Si vas a actualizar varios registros, te recomiendo que hagas un UPDATE controlado mediante SET ROWCOUNT xxx

Donde xxx, es el numero de registros a actualizar

iislas, pediría que escribas una breve explicacion del por que evitar el uso del NOLOCK en la query.

Gracias

damimg

[gnzsoloyo]

La query es un UPDATE, y conforme el manual de referencia oficial (http://msdn.microsoft.com/es-AR/library/ms187373.aspx) cláusulas NOLOCK y READUNCOMMITTED no se deben usar en queries de tipo INSERT, UPDATE y DELETE:

No se pueden especificar READUNCOMMITTED ni NOLOCK en tablas modificadas por operaciones de inserción, actualización y eliminación. El optimizador de consultas de SQL Server omite las sugerencias READUNCOMMITTED y NOLOCK de la cláusula FROM que se aplica a la tabla de destino de una instrucción UPDATE o DELETE.

En el mismo manual hay una interesante nota destacada que dice:

En una versión futura de SQL Server se quitará el uso de las sugerencias READUNCOMMITTED y NOLOCK en la cláusula FROM que se aplican a la tabla de destino de una instrucción UPDATE o DELETE. Evite usar estas sugerencias en este contexto en el nuevo trabajo de desarrollo y planee modificar las aplicaciones que las usan actualmente.

Por lo demás, haceun un UPDATE masivo de una base en caliente, me parece algo extremadamente peligroso, que en todos los DBMS se desaconseja. Es preferible hacerlo off-line.

[damimg]

Iniciado por gnzsoloyo

La query es un UPDATE, y conforme el manual de referencia oficial (http://msdn.microsoft.com/es-AR/library/ms187373.aspx) cláusulas NOLOCK y READUNCOMMITTED no se deben usar en queries de tipo INSERT, UPDATE y DELETE:

En el mismo manual hay una interesante nota destacada que dice:

Por lo demás, haceu un UPDATE masivo de una base en caliente, me parece algo extremadamente peligroso, que en todos los DBMS se desaconseja. Es preferible hacerlo off-line.

Excelente respuesta gnzsoloyo, por determinación de Microsoft la instrucción NOLOCK no debe utilizarse en operaciones del tipo DML en SQL Server 2008.+
En mi experiencia profesional, en cuanto a UPDATE MASIVOS de tablas con millones de registros, me uno a la recomendación de gnzsoloyo, se debe deshabilitar temporalmente la base (modo offline) y luego realizar el UPDATE.

En el caso que no se puede desconectar la DB se deberá realizar un UPDATE SEGMENTADO, es decir, dividiendo el total de los registros a actualizar en porciones iguales para hacer un uso más óptimo del DBMS.

Saludos a la comunidad.

damimg
DBA Specialist

[GeriReshef]

Por supuesto:

Código SQL:

WHERE (M.FechaMovimiento <> I.Fecha_Proceso OR M.FechaMovimiento IS NULL)
 

(y no lo que he escrito)

[Libras]

Estoy en desacuerdo con gnzsoloyo y con damimg de que no se "debe de hace run update masivo" con la base de datos online, entonces como operan las grandes empresas bancarias? dan de baja las bases de datos cuando se hacen los updates de las cuentas?? todo eso se hace en horas donde la base de datos tiene poca utilizacion pero no se tiene porque dejar aislada......ademas con una buena optimizacion de querys, indices y demas se pueden hacer updates de millones de registros en poco tiempo(experiencia profesional)

saludos!