none
Mejorar Velocidad Consulta RRS feed

  • Pregunta

  • Buen dia, tengo la siguiente consulta pero tarda mucho en responder los resultados son 3.000 o 6.000 registros como máximo pero tarda, habrá forma de optimizar la consulta?

    SELECT  a.facperiodo, a.faccodigo, a.facnumero,
    c.munnombre, a.facestrato, d.estnombre,  a.facvencim,
    a.facfactor, a.factipfac, a.faclecant, a.faclecact,
    a.facconsum, a.facatraso, a.facpromedio, a.facemision,
    a.facmunicipio, a.facciclo, e.medfecser, f.perconsini,
    f.perconsfin, d.esttipo, b.usuruta, b.usunombre,
    b.usudireccion, g.barnombre, e.mednumero, a.factransaccion,
    d.esttipo
    FROM facdat_maes as a
    INNER JOIN usuari_maes as b ON (a.faccodigo=b.usucodigo)
    INNER JOIN munici_maes as c ON (a.facmunicipio=c.muncodigo)
    INNER JOIN estrat_maes as d ON (a.facestrato=d.estcodigo)
    LEFT JOIN medido_mv00 as e ON (a.faccodigo=e.medcodigo)
    AND (e.medestado = 'A')
    INNER JOIN perfac_maes as f ON (a.facperiodo=f.perperiodo)
    AND (a.facciclo=f.perciclo) AND (f.perestado IN ('A','C'))
    INNER JOIN barrio_maes as g ON (b.usubarrio=g.barcodigo)
    AND (a.facmunicipio=g.barmunicipi) AND (g.barestado='A')
    WHERE a.facperiodo = periodof
    AND facmunicipio BETWEEN municipio_ini AND municipio_fin
    AND facestrato BETWEEN estrato_ini AND estrato_fin
    AND facciclo BETWEEN ciclo_ini AND ciclo_fin
    AND usuruta BETWEEN ruta_ini AND ruta_fin
    AND a.facestado = 'A'
    AND a.facnumero > 0
    AND a.facusuest IN("A","M")
    AND a.facatraso BETWEEN atraso_ini AND atraso_fin
    ORDER BY b.usumunicip,b.usuruta

    lunes, 6 de abril de 2015 16:25

Respuestas

  • Hola.

    Además del plan de ejecución, sería muy útil contar con las lecturas lógicas que es preciso realizar. Para ello, ejecuta previamente "set statistics io on", y nos pasas ambas cosas, el plan de ejecución y las lecturas lógicas. De ese modo podremos saber cómo mejorar de forma eficaz la consulta.

    De todos modos, además del diagnóstico, sería aconsejable plantearse la funcionalidad que requiere retornar 3.000 registros ordenados, cuando eso es algo que en un aplicativo cliente no tiene mucho sentido. En esa línea, te convendría paginar el resultado.

    Si por el contrario se trata de un listado o un volcado de datos, ahí puede haber más flexibilidad en cuanto a los tiempos de respuesta.

    Roberto, indicarte que emplear una CTE no va a mejorar el rendimiento por sí misma. Sí puede ser que varíe el plan de ejecución y de ese modo se mejore la sentencia, pero a priori, implementar la sentencia con una CTE no tiene por qué tener un efecto positivo en el rendimiento.


    Alberto López Grande
    SQL Server MVP
    Visita mi blog en http://qwalgrande.com
    Sígueme en twitter en http://twitter.com/qwalgrande

    lunes, 6 de abril de 2015 20:03
    Moderador

Todas las respuestas

  • Podrías hacer un CTE que son tablas en memoria que solo traen la información requerida y sobre esta tabla trabajar una nueva consulta

    Ejemplo:

    With CTEEjemplo as (
    
    Select * From TablaGeneral Where Algo
    
    --Contiene 3000 registros
    
    )
    
    Select * from CTEEjemplo where Algo2
    
    --Este segundo select manipula solo los 3000 registros que tienes

    De este modo lo que yo haría sería crear un par de ctes para la consulta a las primeras 2 tablas luego unirlas en un 3er cte y posteriormente solo manipular este último.

    Saludos

    lunes, 6 de abril de 2015 16:48
  • CrissR,

    Buenas tardes, como estas? tendrias el plan de ejecucion de la consulta? que version estas utilizando?

    lunes, 6 de abril de 2015 17:12
  • Podrías hacer un CTE que son tablas en memoria que solo traen la información requerida y sobre esta tabla trabajar una nueva consulta

    Ejemplo:

    With CTEEjemplo as (
    
    Select * From TablaGeneral Where Algo
    
    --Contiene 3000 registros
    
    )
    
    Select * from CTEEjemplo where Algo2
    
    --Este segundo select manipula solo los 3000 registros que tienes

    De este modo lo que yo haría sería crear un par de ctes para la consulta a las primeras 2 tablas luego unirlas en un 3er cte y posteriormente solo manipular este último.

    Saludos


    Hola, no entiendo bien, osea que se crearían esas tablas en memoria siempre? pero los datos van a variar
    lunes, 6 de abril de 2015 17:28
  • CrissR,

    Buenas tardes, como estas? tendrias el plan de ejecucion de la consulta? que version estas utilizando?


    Plan de Ejecución?
    lunes, 6 de abril de 2015 17:28
  • Por ejemplo no es lo mismo hacer una consulta a una sola tabla con un select que ya uniendo 5 o 6 tablas

    Creas un CTE1 para la tabla a, b y c

    Luego creas otro CTE2 donde unes d,e y f

    Y luego creas uno que una el CTE 1 con el CTE 2

    ejemplo

    WITH CTE1 as(
    
    --Te trae solo 10000 registros de el millon que tienes en base de datos de las tablas A,b y c
    
    select a.dato1, a.dato2, b.dato3, c.dato4
    
    FROM tabla1 as a
    
    INNER JOIN Tabla2 as b
    
    ON a.datox = b.datoy
    
    INNER JOIN Tabla3 as c
    
    ON b.datoz = c.Datoa
    
    ),
    
    CTE2 as (
    
    ----Te trae solo 10000 registros de el millon que tienes en base de datos de las tablas d,e y f
    
    select d.dato1, d.dato2, e.dato3, f.dato4
    
    FROM tabla4 as d
    
    INNER JOIN Tabla5 as e
    
    ON d.datox = e.datoy
    
    INNER JOIN Tabla6 as f
    
    ON e.datoz = f.Datoa
    
    WHERE ALGO
    
    ),
    
    --aqui unes los 2 CTEs
    
    CTETotal as (
    
    --Une los 1000 registros del CTE1 con los 10000 del CTE2 y así en memoria solo tienes la union de ambos que 
    
    --diremos que son solo 6000 registros
    
    Select CTE1.Dato1, CTE2.Dato1 as Dato2, CTE1.Dato2 as Dato3, CTE2.Dato3 as Dato4
    
    FROM CTE1,
    
    Inner JOIN CTE2 
    
    ON CTE1.Dato1 = CTE2.Dato1 --Donde dato1 es regularmente algun Id
    
    Where ALGO
    
    )
    
    --Manipulas solo los 6000 registros así optimizas el tiempo de consulta del query
    
    select Dato1, Dato2, Dato3, Dato4
    
    FROM CTETotal
    
    where ALGO


    lunes, 6 de abril de 2015 17:44
  • Aca te paso un print de pantalla de como es un plan de ejecucion... inserta tu consulta en el managment studio y luego presiona el boton que se encuentra marcado con un cuadrado (primero de la izquierda) y pasame ese print de pantalla.

    lunes, 6 de abril de 2015 18:00
  • Hola.

    Además del plan de ejecución, sería muy útil contar con las lecturas lógicas que es preciso realizar. Para ello, ejecuta previamente "set statistics io on", y nos pasas ambas cosas, el plan de ejecución y las lecturas lógicas. De ese modo podremos saber cómo mejorar de forma eficaz la consulta.

    De todos modos, además del diagnóstico, sería aconsejable plantearse la funcionalidad que requiere retornar 3.000 registros ordenados, cuando eso es algo que en un aplicativo cliente no tiene mucho sentido. En esa línea, te convendría paginar el resultado.

    Si por el contrario se trata de un listado o un volcado de datos, ahí puede haber más flexibilidad en cuanto a los tiempos de respuesta.

    Roberto, indicarte que emplear una CTE no va a mejorar el rendimiento por sí misma. Sí puede ser que varíe el plan de ejecución y de ese modo se mejore la sentencia, pero a priori, implementar la sentencia con una CTE no tiene por qué tener un efecto positivo en el rendimiento.


    Alberto López Grande
    SQL Server MVP
    Visita mi blog en http://qwalgrande.com
    Sígueme en twitter en http://twitter.com/qwalgrande

    lunes, 6 de abril de 2015 20:03
    Moderador