CODEWITHSUNDEEP
sqlsql-serversql-server-2008query-optimization
Uri
Uri

Reputation: 2257

How could I optimize this SQL query?

Right now I have this, which retrieves around 16K records: 

DECLARE @LINE_ID INT
SET @LINE_ID = 37
SELECT 
    a.eventType as 'evento', 
    a.tipo as 'tipo', 
    a.nroSoldadura as 'nro soldadura', 
    a.cuentaOdometrica as 'Distancia Absoluta [m]',
    a.x as 'x', 
    a.y as 'y', 
    a.z as 'z', 
    a.zonaGeografica as 'Zona geografica', 
    a.orientacionSoldadura as 'orientacion', 
    a.prof * 100 as 'prof maxima [%]',
    a.longitud as 'long. [mm]',
    a.ancho as 'ancho [mm]',
    a.IntExt as 'INT o EXT',
    a.cluster as 'Cluster',
    a.espesorPared as 'espesor de pared nominal',
    a.longitudEfectiva as 'longitud efectiva',
    a.anchoEfectivo as 'Ancho efectivo',
    a.areaEfectiva as 'Area efectiva',
    a.factorSeguridad as 'Factor de seguridad',
    a.MAOP as 'MAOP (kPa)',
    a.ruptura as 'Ruptura 0.85dL (kPa)',
    a.FER as 'FER',
    NULL as 'Diametro maximo',
    NULL as 'Diametro minimo',
    NULL as 'Ovalizacion',
    NULL as 'Ovalizacion axial',
    a.orientacionSoldadura as 'Orientacion soldadura',
    a.velocidadHerramienta as 'Velc Herr',
    a.comentarios as 'comentarios'
FROM ILI_IMPORT_MFL as a
JOIN ILI_INSPECTION as b ON a.ILI_INSPECTION_ID = b.ILI_INSPECTION_ID
WHERE 
    a.ID IN (
        SELECT TOP 1 c.ID
        FROM ILI_IMPORT_MFL as c 
        JOIN ILI_INSPECTION as d ON c.ILI_INSPECTION_ID = d.ILI_INSPECTION_ID
        WHERE a.nroSoldadura = c.nroSoldadura
            AND d.LINE_ID = @LINE_ID
        ORDER BY d.BEGIN_DATE 
    )
    AND b.LINE_ID = @LINE_ID
ORDER BY a.nroSoldadura, b.BEGIN_DATE

The join between ILI_IMPORT_MFL and ILI_INSPECTION is there because the ILI_INSPECTION contains a date and a line ID that I need.
Right now this query takes around 6 minutes to execute because of the subquery. The subquery is in there because there may be more than one row for each nroSoldadura, and I only want the oldest row of them.

I'm using SQL Server 2008.
How could I fix that? Thanks.

EDIT: Tried adding indexes to the nroSoldadura and BEGIN_DATE columns, and brought the query time down to 30 seconds. Still would be nice to see a possible optimization in order to learn.

Upvotes: 0

Views: 161

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269613

You want to use row_number() for this. You need to place it in a subquery. I think this is what you want:

select * from (
SELECT 
    a.eventType as 'evento', 
    a.tipo as 'tipo', 
    a.nroSoldadura as 'nro soldadura', 
    a.cuentaOdometrica as 'Distancia Absoluta [m]',
    a.x as 'x', 
    a.y as 'y', 
    a.z as 'z', 
    a.zonaGeografica as 'Zona geografica', 
    a.orientacionSoldadura as 'orientacion', 
    a.prof * 100 as 'prof maxima [%]',
    a.longitud as 'long. [mm]',
    a.ancho as 'ancho [mm]',
    a.IntExt as 'INT o EXT',
    a.cluster as 'Cluster',
    a.espesorPared as 'espesor de pared nominal',
    a.longitudEfectiva as 'longitud efectiva',
    a.anchoEfectivo as 'Ancho efectivo',
    a.areaEfectiva as 'Area efectiva',
    a.factorSeguridad as 'Factor de seguridad',
    a.MAOP as 'MAOP (kPa)',
    a.ruptura as 'Ruptura 0.85dL (kPa)',
    a.FER as 'FER',
    NULL as 'Diametro maximo',
    NULL as 'Diametro minimo',
    NULL as 'Ovalizacion',
    NULL as 'Ovalizacion axial',
    a.orientacionSoldadura as 'Orientacion soldadura',
    a.velocidadHerramienta as 'Velc Herr',
    a.comentarios as 'comentarios',
    ROW_NUMBER() over (partition by a.nroSoldadura order by begin_date desc) as seqnum
FROM ILI_IMPORT_MFL as a
JOIN ILI_INSPECTION as b ON a.ILI_INSPECTION_ID = b.ILI_INSPECTION_ID
where b.line_id = @line_id
)
where seqnum = 1

Upvotes: 0

Eric Petroelje
Eric Petroelje

Reputation: 60498

You could try using ROW_NUMBER() ... PARTITION BY to replace your subquery:

DECLARE @LINE_ID INT
SET @LINE_ID = 37
SELECT 
    a.eventType as 'evento', 
    a.tipo as 'tipo', 
    a.nroSoldadura as 'nro soldadura', 
    a.cuentaOdometrica as 'Distancia Absoluta [m]',
    a.x as 'x', 
    a.y as 'y', 
    a.z as 'z', 
    a.zonaGeografica as 'Zona geografica', 
    a.orientacionSoldadura as 'orientacion', 
    a.prof * 100 as 'prof maxima [%]',
    a.longitud as 'long. [mm]',
    a.ancho as 'ancho [mm]',
    a.IntExt as 'INT o EXT',
    a.cluster as 'Cluster',
    a.espesorPared as 'espesor de pared nominal',
    a.longitudEfectiva as 'longitud efectiva',
    a.anchoEfectivo as 'Ancho efectivo',
    a.areaEfectiva as 'Area efectiva',
    a.factorSeguridad as 'Factor de seguridad',
    a.MAOP as 'MAOP (kPa)',
    a.ruptura as 'Ruptura 0.85dL (kPa)',
    a.FER as 'FER',
    NULL as 'Diametro maximo',
    NULL as 'Diametro minimo',
    NULL as 'Ovalizacion',
    NULL as 'Ovalizacion axial',
    a.orientacionSoldadura as 'Orientacion soldadura',
    a.velocidadHerramienta as 'Velc Herr',
    a.comentarios as 'comentarios'
    ROW_NUMBER() OVER (PARTITION BY a.ID ORDER BY d.BEGIN_DATE) As row
FROM ILI_IMPORT_MFL as a
JOIN ILI_INSPECTION as b ON a.ILI_INSPECTION_ID = b.ILI_INSPECTION_ID
WHERE 
    row = 1
    AND b.LINE_ID = @LINE_ID
ORDER BY a.nroSoldadura, b.BEGIN_DATE

Upvotes: 1

Related Questions