Como optimizar un UPDATE a pesar de tener INDICES

[LUISESPOCH]

Hola amigos todos tengo un serio problema haber si me pueden echar una mano en esto:

Tengo un SP que contiene el siguiente bloque de Update para 1 solo campo, que se esta tomando mucho tiempo. 10 millones de registros se toma más de 10 horas y no se cómo optimizarlo.

UPDATE A
SET CODIGO_USUARIO=B.CODIGO_ANTERIOR_USUARIO
FROM TABLA1 A, OTRABASEDATOS..TABLA2 B
WHERE
A.CODIGO_USUARIO_MBA=B.CODIGO_HOMOLOGADO_USUARIO
AND A.FECHA_INICIO = @FECHA_I

El campo que actualiza es un varchar(25), ejemplo "usuarioXYZ", usando como codigo de busqueda otro varchar(25) un valor como "045712"



Cuando uso sp_lock con el Id de mi proceso tengo 4891 filas que indican bloqueos de pagina. Por lo general con la siguiente informacion:

SPID
78 -->mi proceso ene ejcucion
DBID
15 --> mibd
OBJID
1518784618 ??
IndId
0
Type
PAG
Resource
5:20496537
Mode
X
Status
GRAND



He creado indices para cada tabla utilizando como campos las columnas del WHERE para cada tabla. Osea en todas 2 indices:

INDICE PARA TABLA1:
IDX_FILTRO_COMUN (CODIGO_USUARIO_MBA, FECHA_INICIO)

INDICE PARA TABLA2:
IDX_FILTRO_COMUN (CODIGO_HOMOLOGADO_USUARIO)


He procedido incluso a bajar la data de la tabla 2 que proviene de otra BD y ponerla en una tabla de la misma BD de la tabla 1, pero no percibo ventaja alguna.

Trabajo con SQLServer 2000

Por favor si alguien me da una sugerencia al respecto, le quedare agradecido.

Gracias a tod@s por la ayuda

LE

[iislas]

¿Porque no utilizas INNER JOIN?, ¿Que tipo de dato es: A.FECHA_INICIO?, si fuera DATETIME, ¿porque lo comparas con = (igual)?

[LUISESPOCH]

Iniciado por iislas

¿Porque no utilizas INNER JOIN?, ¿Que tipo de dato es: A.FECHA_INICIO?, si fuera DATETIME, ¿porque lo comparas con = (igual)?

Saludo IIslas, gracias tu como siempre dandome una mano...

Mira el tipo de dato de A.FECHA_INICIO es DateTime, y la variable de entrada del SP @FECHA_I es SMALLDATETIME.
No uso inner join porque pensé mantenerle la programación existente.

UPDATE A
SET CODIGO_USUARIO=B.CODIGO_ANTERIOR_USUARIO
FROM TABLA1 A, OTRABASEDATOS..TABLA2 B
WHERE
A.CODIGO_USUARIO_MBA=B.CODIGO_HOMOLOGADO_USUARIO
AND A.FECHA_INICIO = @FECHA_I


Por favor algun detalle adicional?.. Te agradecería mucho me orientes..es un proceso critico.
Te lo agradezco

LE

[iislas]

Cambi la forma de unir tus tablas con INNER JOIN y compara las fechas con >= y <=

[LUISESPOCH]

Iniciado por iislas

Cambi la forma de unir tus tablas con INNER JOIN y compara las fechas con >= y <=

Haber si te comprendi..., lo del Inner OK pero de la fecha no te cacho bien..porque no quiero en un rango sino de un día especifico. Acaso debo aumentarme a mi fecha de entraga las horas minutos y segundos..algo asi
@FECHA_I1 = '2012-06-01 00:00:00'
@FECHA_I2 = '2012-06-01 23:59:59'

Algo asi...a esto te refieres ?

UPDATE A
SET CODIGO_USUARIO=B.CODIGO_ANTERIOR_USUARIO
FROM TABLA1 A inner join OTRABASEDATOS..TABLA2 B
on
A.CODIGO_USUARIO_MBA=B.CODIGO_HOMOLOGADO_USUARIO
AND (A.FECHA_INICIO >= @FECHA_I1 and A.FECHA_INICIO <= @FECHA_I2 )

[iislas]

Primero has un SELECT (antes de aplicar el update) y mide los tiempos.

AND (A.FECHA_INICIO >= @FECHA_I and A.FECHA_INICIO <= @FECHA_I)

[Libras]

hacer un update que tarde poco tiempo esta algo complicado y mas si manejas millones de registros, lo que podrias hacer y es como una sugerencia es que pongas en una tabla temporal los registros a actualiza(no todos los datos solo los id's) y que selecciones primero digamos el top 10 percent de esa tabla y lo actualices en tu tabla maestra, despues que elimines ese top 10 percent de la tabla temporal y que repitas el proceso varias veces......algo asi:

Código SQL:

UPDATE tabla A
SET campo1=campo2
WHERE tabla A.id EXISTS (SELECT top 10 percent id FROM tabla B)
 
DELETE top 10 percent FROM tabla B
 
UPDATE tabla A
SET campo1=campo2
WHERE tabla A.id EXISTS (SELECT top 10 percent id FROM tabla B)
 
DELETE top 10 percent FROM tabla B
 
UPDATE tabla A
SET campo1=campo2
WHERE tabla A.id EXISTS (SELECT top 10 percent id FROM tabla B)
 
DELETE top 10 percent FROM tabla B
 

y asi sucesivamente, quizas sea mucho codigo pero estas haciendo un proceso grande en varios mini procesos que en teoria tardarian menos :), digo es una idea que aplique en un proceso que tardaba casi 8 horas y ahora nada mas tarda 3 :P jejejejeje.

saludos!

[LUISESPOCH]

Iniciado por iislas

Primero has un SELECT (antes de aplicar el update) y mide los tiempos.

AND (A.FECHA_INICIO >= @FECHA_I and A.FECHA_INICIO <= @FECHA_I)

Te comento IIslas que hice la prueba de los select con el query original y el modificado y me sale

Original
1216963 records en 00:12:15

Modificado
1216963 records en 00:14:03

Que será??

[iislas]

Has esto:

SET STATISTICS IO ON

Vuelves a ejecutar tu codigo y muestranos las lecturas que hace.

[LUISESPOCH]

IIslas aqui mi reporte

SELECT *
FROM TABLA1 A INNER JOIN OTRABD..TABLA2 B ON
A.CODIGO_USUARIO = B.CODIGO_HOMOLOGADO_USUARIO
AND (A.FECHA_INICIO >= '2012/04/10' AND A.FECHA_INICIO <= '2012/04/10' )

Las estadist., me arroja este resultado..

Table 'TABLA1. Scan count 1, logical reads 539656, physical reads 0, read-ahead reads 539706.
Table 'TABLA2. Scan count 1, logical reads 202, physical reads 1, read-ahead reads 201.

(1216963 row(s) affected)

???

Ojo que esta data es solo una muestra, en realidad la data totoal es mas de 15 millones de registros.

[Libras]

y la idea que te plante no es viable? analizando las estadisticas te puedo decir que tu tablas estan bien indexadas ya que solo muestran las lecturas logicas a las mismas no las fisicas(lo cual consume mas tiempo)

Saludos!

[iislas]

Aqui estas haciendo una comparacion de CHAR, por favor, ya lo hemos dicho, cambia tu estructura de las fechas.

AND (A.FECHA_INICIO >= '2012/04/10' AND A.FECHA_INICIO <= '2012/04/10' )

Debe ser:

AND (A.FECHA_INICIO >= '20120410' AND A.FECHA_INICIO <= '20120410' )

[ingelenalopez]

Cuidado también con los índices que tienes, el update elimina y reinserta la nueva fila con los cambios... Quiere decir que si tu tabla tiene 3 índices estás insertando 3 veces, aplica lo mismo para los update.

[LUISESPOCH]

Iislas gracias por reiterar lo del tipo fechas, pero te comento algo,..la ejecución de mi sp lo hago desde un dts, y para esto yo leo la Fecha desde una variable global con ese formato que te indique antes, no del tipo yyyymmdd, a pesar que en mi sp el tipo de dato que tiene mi parametro es datetime, es por eso que en el sp le envio el dato como viene.

Ingelenalopez, saludos, lo que respecta a los indices que triplican aparentemente las filas en los insert y update, entonces lo recomendable seria eliminarlos cuando haga este tipo de operaciones (insert, update) y volverlos a crear cuando solo haga Select??

Libral, la actualización por bloques en función de porcentajes tengo una observación,cuando intente obtener el 10% me trajo un valor decimal en la cantidad de registros totales, por eso mi inquietud y duda de pronto se me quedan fuera algunos registros.

[iislas]

Si tu fecha viene asi yyyy-mm-dd, has un CAST o CONVERT, antes de pasarlo al proceso

Intentaste hacer un simple select con el formato ANSI?

[LUISESPOCH]

No he intentado aun con el formato ANSI, espero que al poner el cast en mi WHERE no afecte su performance. ¿¿..¿?

Debo aclarar que en la data este campo Fecha esta en formato yyyy-mm-dd hh:mm:ss

Por cierto IIslas la función que encontré para transformar a ANSI fue esta...

SELECT CONVERT(CHAR(8), @dt, 112) + '.' + REPLACE(CONVERT(CHAR(8), @dt, 108), ':', '')
Tomado de..
http://social.msdn.microsoft.com/For...9-1ceada2c11fa
(sin ánimos de hacer publicidad al link, pero eso fue lo que encontré)

...de ser útil esta función de conversión de fechas, no te parecería que se haría muy pesado el tiempo de ejecución y afectaría mas mi performance ??

[iislas]

Luis

"Debo aclarar que en la data este campo Fecha esta en formato yyyy-mm-dd hh:mm:ss", eso es lo que tu crees, pero las fechas siempre se almacenan de forma NUMERICA.

Mi propuesta fue:

AND (A.FECHA_INICIO >= '20120410' AND A.FECHA_INICIO <= '20120410' )

No que aplicaras CAST sobre tu campo.

[LUISESPOCH]

IIslas, saludos
En este momento estoy otra vez en los servers,.. dame un par de horitas para informarte como me fue...

[LUISESPOCH]

Amigo Isslas, te comento sigo haciendo pruebas...y te comento que investigando la recomendacion que me hacen tambien es DROPEAR los indcies antes de la carga, y RESTAURARLOS luego.
Además voy a ver si discrimino toda la data en una TMP con los registros de esa fecha, de manera que yo pueda trabajar con estos sin necesidad de poner en el WHERE el filtro de fechas, asi reduzco el procesamiento.