tiempo - SQL Duplicate Delete Query sobre millones de filas para el rendimiento

rendimiento sql explicado pdf

Question

Esto ha sido una aventura. Comencé con la consulta duplicada de bucle ubicada en mi pregunta anterior , pero cada bucle repasaría los 17 millones de registros , lo que significa que llevaría semanas (solo ejecutar *select count * from MyTable* lleva a mi servidor 4:30 minutos con MSSQL 2005) . Destellé información de este sitio y en esta publicación .

Y he llegado a la consulta a continuación. La pregunta es, ¿es este el tipo correcto de consulta para ejecutar en 17 millones de registros para cualquier tipo de rendimiento? Si no es así, ¿qué es?

SQL QUERY:

DELETE tl_acxiomimport.dbo.tblacxiomlistings
WHERE RecordID in 
(SELECT RecordID
    FROM tl_acxiomimport.dbo.tblacxiomlistings
    EXCEPT
    SELECT RecordID
    FROM (
        SELECT RecordID, Rank() over (Partition BY BusinessName, latitude, longitude,           Phone ORDER BY webaddress DESC, caption1 DESC, caption2 DESC ) AS Rank
    FROM tl_acxiomimport.dbo.tblacxiomlistings
    ) al WHERE Rank = 1)

Answer 1

¿Así que borras todos los registros que no están clasificados primero? Podría valer la pena comparar una combinación contra una subconsulta de top 1 (que también podría funcionar en 2000, ya que el rango es 2005 y superior únicamente)

¿Necesita eliminar todos los duplicados en una sola operación? Supongo que estás realizando algún tipo de tarea de limpieza, podrías hacerlo por partes.

Básicamente, crea un cursor que bucles todos los registros (lectura sucia) y elimina los engaños para cada uno. En general será mucho más lento, pero cada operación será relativamente mínima. Entonces su mantenimiento se convierte en una tarea de fondo constante en lugar de un lote nocturno.

Answer 2

¿No sería más simple de hacer?

DELETE tl_acxiomimport.dbo.tblacxiomlistings
WHERE RecordID in 
(SELECT RecordID
   FROM (
        SELECT RecordID,
            Rank() over (Partition BY BusinessName,
                                  latitude,
                                  longitude,
                                  Phone
                         ORDER BY webaddress DESC,
                                  caption1 DESC,
                                  caption2 DESC) AS Rank
        FROM tl_acxiomimport.dbo.tblacxiomlistings
        )
  WHERE Rank > 1
  )

Answer 3

17 millones de registros no son nada. Si son solo las 4:30 para hacer un recuento selectivo (*), entonces hay un problema grave, probablemente relacionado con la falta de memoria en el servidor o con un procesador realmente antiguo.

Para el rendimiento, arregle la máquina. Bombear hasta 2 GB. La RAM es tan barata actualmente que su costo es mucho menor que su tiempo.

¿Se está agotando el procesador o el disco cuando se realiza esa consulta? Si no, entonces algo está bloqueando las llamadas. En ese caso, podría considerar poner la base de datos en modo de usuario único durante el tiempo que lleva ejecutar la limpieza.

Answer 4

Además de usar truncar como se sugiere, he tenido la mejor suerte al utilizar esta plantilla para eliminar muchas filas de una tabla. No recuerdo nada, pero creo que el uso de la transacción ayudó a que no creciera el archivo de registro, aunque podría haber sido otra razón, pero no estoy seguro. Y normalmente cambio el método de registro de transacciones a simple antes de hacer algo como esto:

SET ROWCOUNT 5000 WHILE 1 = 1 BEGIN begin tran DELETE FROM ??? WHERE ??? IF @@rowcount = 0 BEGIN COMMIT BREAK END COMMIT END SET ROWCOUNT 0

Answer 5

Algo pasa con su base de datos, servidor, almacenamiento o alguna combinación de los mismos. 4:30 para un conteo selecto * parece MUY alto.

Ejecute un DBCC_SHOWCONTIG para ver qué tan fragmentada está su tabla, esto podría causar un gran golpe de rendimiento sobre una tabla de ese tamaño.

Además, para agregar al comentario de RyanKeeter, ejecute el plan de presentación y, si hay escaneos de tabla, cree un índice para el campo PK en esa tabla.

Answer 6

Ejecuta esto en el analizador de consultas:

SET SHOWPLAN_TEXT ON

Luego, solicite al analizador de consultas que ejecute su consulta. En lugar de ejecutar la consulta, SQL Server generará un plan de consulta y lo colocará en el conjunto de resultados.

Muéstranos el plan de consulta.

Answer 7

Esto se ve bien, pero podría considerar seleccionar sus datos en una tabla temporal y usarlos en su declaración de eliminación. Noté grandes ganancias de rendimiento al hacer esto en lugar de hacerlo todo en esa consulta.

Answer 8

La sugerencia anterior de seleccionar en una tabla temporal primero es su mejor opción. También podría usar algo como:

set rowcount 1000

antes de ejecutar su eliminación. Dejará de ejecutarse cuando elimine las 1000 filas. Luego ejecútalo una y otra vez hasta que borres 0 registros.

Answer 9

Ver el QueryPlan ayudaría.

¿Es esto factible?

SELECT m.*
into #temp
FROM tl_acxiomimport.dbo.tblacxiomlistings m 
inner join (SELECT RecordID, 
                   Rank() over (Partition BY BusinessName, 
                                             latitude,  
                                             longitude,            
                                             Phone  
                                ORDER BY webaddress DESC,  
                                         caption1 DESC,  
                                         caption2 DESC ) AS Rank
              FROM tl_acxiomimport.dbo.tblacxiomlistings
           ) al on (al.RecordID = m.RecordID and al.Rank = 1)

truncate table tl_acxiomimport.dbo.tblacxiomlistings

insert into tl_acxiomimport.dbo.tblacxiomlistings
     select * from #temp

Answer 10

si lo recibo correctamente tu consulta es la misma que

DELETE tl_acxiomimport.dbo.tblacxiomlistings
FROM
    tl_acxiomimport.dbo.tblacxiomlistings allRecords
    LEFT JOIN (   
        SELECT RecordID, Rank() over (Partition BY BusinessName, latitude, longitude, Phone ORDER BY webaddress DESC, caption1 DESC, caption2 DESC ) AS Rank
        FROM tl_acxiomimport.dbo.tblacxiomlistings
        WHERE Rank = 1) myExceptions
    ON allRecords.RecordID = myExceptions.RecordID
WHERE
    myExceptions.RecordID IS NULL

Creo que debería funcionar más rápido, tiendo a evitar usar la cláusula "IN" a favor de JOINs cuando sea posible.

En realidad, puede probar la velocidad y los resultados de forma segura simplemente llamando a SELECT * o SELECT COUNT(*) en la parte FROM como, por ejemplo,

SELECT *
FROM
    tl_acxiomimport.dbo.tblacxiomlistings allRecords
    LEFT JOIN (   
        SELECT RecordID, Rank() over (Partition BY BusinessName, latitude, longitude, Phone ORDER BY webaddress DESC, caption1 DESC, caption2 DESC ) AS Rank
        FROM tl_acxiomimport.dbo.tblacxiomlistings
        WHERE Rank = 1) myExceptions
    ON allRecords.RecordID = myExceptions.RecordID
WHERE
    myExceptions.RecordID IS NULL

Esa es otra razón por la que preferiría el enfoque JOIN, espero que ayude

Answer 11

Recuerde que al realizar una eliminación grande, lo mejor es tener primero una buena copia de seguridad (y generalmente también copio los registros eliminados en otra tabla, por si acaso, necesito recuperarlos de inmediato).