Base de datos SQL Server 2014. Mesa con 200 millones de filas.

Consulta muy grande con cláusula HUGE IN.

Originalmente escribí esta consulta para ellos, pero han aumentado la cláusula IN a más de 700 entradas. El CTE parece innecesario porque he omitido todas las columnas seleccionadas y sus transformaciones de subcadena () para simplificar.

La atención se centra en la cláusula IN . 700+ pares de estos.

WITH cte AS (
           SELECT *      
           FROM AODS-DB1B
           WHERE  
           Source+'-'+Target
    IN
    (
    'ACY-DTW',
    'ACY-ATL',
    'ACY-ORD',
    :
    : 700+ of these pairs
    :
    'HTS-PGD',
    'PIE-BMI',
    'PGD-HTS'
    )

    )

    SELECT *
    FROM cte
    order by Source, Target, YEAR, QUARTER

Cuando se ejecuta, esta consulta dispara la CPU al 100% durante horas, no de forma inesperada.

Hay índices en todas las columnas involucradas.

Pregunta 1 : ¿Existe una forma mejor o más eficaz de realizar esta consulta que no sea la enorme cláusula IN? ¿Serían mejores 700 UNION ALL?

Pregunta 2 : cuando se ejecuta esta consulta, crea un Session_ID que contiene 49 "subprocesos" (49 procesos que tienen todos el mismo Session_ID). Cada uno de ellos es una instancia de esta consulta con su "Comando" siendo este texto de consulta.

21 de ellos SUSPENDIDOS, 14 de ellos CORRER y 14 de ellos CORRER.

Esto cambia rápidamente a medida que se ejecuta la tarea.

¿QUÉ diablos está pasando allí? ¿Este SQL Server está dividiendo la consulta en pedazos para trabajar en ella?

3
Kirby L. Wallace 25 ene. 2016 a las 18:52

4 respuestas

La mejor respuesta

Le recomiendo que almacene sus más de 700 cadenas en una tabla permanente, ya que generalmente se percibe como una mala práctica almacenar tantos metadatos en un script. Puedes crear la tabla así:

CREATE TABLE dbo.LookUp(Source varchar(250), Target varchar(250))

CREATE INDEX IX_Lookup_Source_Target on dbo.Lookup(Source,Target)

INSERT INTO dbo.Lookup (Source,Target)
SELECT 'ACY','DTW'
UNION
SELECT 'ACY','ATL'
.......

Y luego puedes simplemente unirte a esta mesa:

SELECT * FROM [AODS-DB1B] a
INNER JOIN dbo.Lookup lt ON lt.Source = a.Source 
                         AND lt.Target=a.Target
ORDER BY Source, Target, YEAR, QUARTER

Sin embargo, aún mejor sería normalizar la tabla AODS-DB1B y almacenar los valores SourceId y TargetId INT en su lugar, con los valores VARCHAR almacenados en las tablas Source y Target . Luego, puede escribir una consulta que solo realice comparaciones de enteros en lugar de comparaciones de cadenas y esto debería ser mucho más rápido.

9
Alex 25 ene. 2016 a las 16:11

Me gusta la respuesta de jaco

Tener un índice de origen, destino

Puede valer la pena intentarlo

where ( source = 'ACY' and target in ('DTW', 'ATL', 'ORD') )
   or ( source = 'HTS' and target in ('PGD') )
0
paparazzo 26 ene. 2016 a las 06:11

Puede crear una tabla temporal con todos esos valores y luego UNIRSE a esa tabla, haría que el proceso sea mucho más rápido

0
jthalliens 25 ene. 2016 a las 16:00

Ponga todos sus códigos en una tabla temporal (o permanente si es apropiado) .....

SELECT *      
FROM AODS-DB1B
INNER JOIN NEW_TABLE ON Source+'-'+Target = NEWTABLE.Code
WHERE  
...
...
2
AntDC 25 ene. 2016 a las 15:59