Tengo una tabla grande en el servidor SQL que contiene 136.651.894 de filas. Hace un mes se insertaron 7.668.309 nuevas filas con un error en un campo. En el momento de la inserción creo una tabla de copia para asegurarme de que esto no suceda, pero nadie vio el error y dejé caer la tabla una semana después del proceso de carga.

Para eliminar las filas, creo un procedimiento que selecciona los valores máximos por myID de la tabla original (esto se debe a que el error fue que agregamos más ceros en el valor del campo, por lo que el valor máximo es incorrecto y este registro debe eliminarse) , y en una tabla de respaldo busco en estos registros myID y valor y elimino la fila.

Por ejemplo, una fila es:

ID myId Valor Otros campos ...
2 2345 25948238400 Otros valores ...

El procedimiento es:

CREATE PROCEDURE [dbo].[P_DELETE_ROWS] 
AS BEGIN
    DECLARE @myId VARCHAR(22)
    DECLARE @value VARCHAR(20)

  DECLARE c_max CURSOR 
  FOR
    SELECT myId,max ([value]) as maxValue
    FROM t1Original
    group by  myId
    order by maxValuedesc

  OPEN c_max
    FETCH NEXT FROM c_max
    INTO @myId ,@value

  WHILE @@FETCH_STATUS = 0  
    BEGIN
        print 'Deleting '+@myId+' y caudal '+@value +''
        DELETE FROM [t1OriginalCopy] WHERE myId=@myIdAND value=@value
        FETCH NEXT FROM c_max 
        INTO @myId,@value
    END

  CLOSE c_maxs
  DEALLOCATE c_max
END

El problema de esto es que lleva demasiado tiempo, ahora es más de 1 día de ejecución ...

¿Cómo puedo mejorar el rendimiento de este proceso?

0
mrc 14 dic. 2016 a las 11:59

2 respuestas

La mejor respuesta

Utilice DENSE_RANK en lugar de CURSOR.

Prueba esto

;WITH cte
     AS (SELECT Dense_rank()OVER(partition BY myId ORDER BY value DESC) rn,*
         FROM   [t1OriginalCopy])
DELETE FROM cte
WHERE  rn = 1 

Si la tabla (t1OriginalCopy) se utilizará en paralelo en alguna otra operación, es posible que deba dividir la eliminación en lotes para evitar bloqueos.

1
Pரதீப் 14 dic. 2016 a las 09:08

Es posible que el servidor esté sobrecargado y no pueda realizar otras tareas si intenta eliminarlas todas a la vez, así que hágalo en lotes que pueda detener y continuar más tarde si es necesario. Ajuste 'top (100)' para cambiar el tamaño del lote, dependiendo de cómo lo gestione el servidor.

Declare @rcount int=1 Select MyID, max([Value]) as maxvalue into #temp from t1Origina group by myID while @rcount>0 begin Delete top (100) o FROM t1Origina o inner join #temp t on o.myId = t.myId and o.value = t.maxvalue set @rcount=@@rowcount END Drop #temp

0
cloudsafe 14 dic. 2016 a las 12:28