none
Optimizar consulta SQL Server RRS feed

  • Pregunta

  • Hola, buen día!

    Quisiera saber como puedo optimizar mi siguiente consulta, ya que tarda demasiado tiempo en cargar los datos.

     SELECT        PEDIDOS.PNUMERO, PEDIDOS.PLOTE, PEDIDOS.PLOTEALT, PEDIDOS.PXDEX, PEDIDOS.PMODELO, PEDIDOS.PCOLOR, PEDIDOS.PCLVCLTE, PEDIDOS.PAMDPEDIDO, PEDIDOS.PAMDENTREG, 
                                 PEDIDOS.PSEMENTREG, PEDIDOS.PTOTPARES, PEDIDOS.PPED01, PEDIDOS.PPED02, PEDIDOS.PPED03, PEDIDOS.PPED04, PEDIDOS.PPED05, PEDIDOS.PPED06, PEDIDOS.PPED07, PEDIDOS.PPED08, 
                                 PEDIDOS.PPED09, PEDIDOS.PPED10, PEDIDOS.PPED11, PEDIDOS.PPED12, PEDIDOS.PPED13, PEDIDOS.PPED14, PEDIDOS.PPED15, PEDIDOS.PPED16, LOTESW.LCOLORFIJO, LOTESW.LPARTESREQ, 
                                 LOTESW.LPARESCUBR, INSUMOS.INUMERO, INSUMOS.INOMBRE,INSUMOS.ILINEA, INSUMOS.ICERRARSIG, INSUMOS.ITIPOUNI, INSUMOS.IPARTESCON, INSUMOS.ITALLAINI, INSUMOS.IMANEJACOL, 
                                 INSUMOS.IEXREALOG, INSUMOS.ICOSTOULT, INSUMOS.IAMDCOSULT, INSUMOS.ITIPOUNI, LOTESW.LCLAVEPI, INSUMOS.IDIRINDIR, INSUMOS.IDESGLOSAR, INSUMOS.IOPCIONAL, INSUMOS.ISUMARMEDS, 
                                 INSUMOS.IAGRUPA04,PEDIDOS.TALLA
                                 FROM            
                                    LOTESW INNER JOIN
                                    PEDIDOS ON LOTESW.LPEDIDO = PEDIDOS.PNUMERO AND LOTESW.LLOTE = PEDIDOS.PLOTE AND PEDIDOS.EMPRESA=LOTESW.EMPRESA INNER JOIN
                                    MOACIN ON LOTESW.LPROINSU = MOACIN.OAINUMERO AND LOTESW.LMODELO = MOACIN.ONUMERO AND MOACIN.EMPRESA=PEDIDOS.EMPRESA INNER JOIN
                                    INSUMOS ON MOACIN.OAINUMERO = INSUMOS.INUMERO AND LOTESW.LPROINSU = INSUMOS.INUMERO AND INSUMOS.EMPRESA=MOACIN.EMPRESA

                                 WHERE        (MOACIN.OCLAVE = 'I') AND  (LOTESW.LCLAVEPI = N'I') AND (PEDIDOS.PNUMERO = @PNUMERO) AND (PEDIDOS.PLOTE = @PLOTE) AND LOTESW.EMPRESA='" + Global.ID_Fabrica + @"'
                                 GROUP BY PEDIDOS.PNUMERO, PEDIDOS.PLOTE, PEDIDOS.PLOTEALT, PEDIDOS.PXDEX, PEDIDOS.PMODELO, PEDIDOS.PCOLOR, PEDIDOS.PCLVCLTE, PEDIDOS.PAMDPEDIDO, PEDIDOS.PAMDENTREG, 
                                                         PEDIDOS.PSEMENTREG, PEDIDOS.PTOTPARES, PEDIDOS.PPED01, PEDIDOS.PPED02, PEDIDOS.PPED03, PEDIDOS.PPED04, PEDIDOS.PPED05, PEDIDOS.PPED06, PEDIDOS.PPED07, PEDIDOS.PPED08, 
                                                         PEDIDOS.PPED09, PEDIDOS.PPED10, PEDIDOS.PPED11, PEDIDOS.PPED12, PEDIDOS.PPED13, PEDIDOS.PPED14, PEDIDOS.PPED15, PEDIDOS.PPED16, LOTESW.LCOLORFIJO, LOTESW.LPARTESREQ, 
                                                         LOTESW.LPARESCUBR, INSUMOS.INUMERO, INSUMOS.INOMBRE, INSUMOS.ILINEA, INSUMOS.ICERRARSIG, INSUMOS.ITIPOUNI, INSUMOS.IPARTESCON, INSUMOS.ITALLAINI, INSUMOS.IMANEJACOL, 
                                                         INSUMOS.IEXREALOG, INSUMOS.ICOSTOULT, INSUMOS.IAMDCOSULT, INSUMOS.ITIPOUNI, LOTESW.LCLAVEPI, INSUMOS.IDIRINDIR, INSUMOS.IDESGLOSAR, INSUMOS.IOPCIONAL, INSUMOS.ISUMARMEDS, 
                                                         INSUMOS.IAGRUPA04,PEDIDOS.TALLA
                               
                                 ORDER BY 
                                    CASE WHEN substring(INSUMOS.INOMBRE, 1, 1) = '*'  THEN 
                                        substring(INSUMOS.INOMBRE, 2, LEN(INSUMOS.INOMBRE)) 
                                    else 
                                        INSUMOS.INOMBRE  
                                    END


    JK

    martes, 24 de julio de 2018 18:40

Respuestas

  • No se ve si a la izquierda hay alguna otra cosa que tenga "peso", pero entre los bloques indicados hay dos con un "RID lookup" e "Index Seek" que son los más pesados. Dado que están entre medias de los "nested loops", significa que son debidos a los JOIN entre tablas. Y dado que hay cuatro tablas y cuatro nested loops, significa que los joins son poco eficientes y los está resolviendo mediante una comparación entre todos los elementos de cada par de tablas que se unen. Esto indicaría que no hay índices en las tablas que sean capaces de cubrir las condiciones que hay en los ON de los JOIN. En consecuencia, recomendación: Utilizar el DETA (database engine tuning advisor) y dejarle que te recomiende los índices adecuados. Si no lo has usado nunca, sigue algún tutorial porque la primera vez es complicado: hay que hacer una captura con el template de Tuning del Profiler y luego alimentar con ella al DETA.

    • Propuesto como respuesta Pablo RubioModerator viernes, 27 de julio de 2018 18:40
    • Marcado como respuesta JuanK1916 lunes, 13 de agosto de 2018 19:55
    miércoles, 25 de julio de 2018 17:30

Todas las respuestas

  • Desde SQL Server Management Studio, usa la opción de "ver plan de ejecución", y fíjate en los distintos pasos que muestra. En cada bloque figura el porcentaje del coste de la sentencia que se debe a ese bloque. Para optimizarla, hay que centrarse en los bloques que tengan la mayor parte del coste. Viendo el tipo de bloque, se toma una decisión acerca de cómo optimizarlo. Lo más común es que se pierda tiempo en barridos de tabla ("table scan"). Estos se optimizan añadiendo índices (puedes usar el Database Engine Tuning Advisor para que te recomiende los índices).

    Otras cosas no son tan obvias. Por ejemplo, si se pierde mucho tiempo en agrupar u ordenar, esto es debido al volumen de datos que se agrupa u ordena. En tu caso, por ejemplo, puedes encontrarte con un problema en el Group By (te lo dirá el plan de ejecución) debido a que agrupas por muchos campos. Si esto lo haces únicamente para poderlos poner en el Select, pero en realidad sabes que la mayor parte de esos campos son siempre iguales (porque vienen de un Join) y realmente no hay que agrupar nada, entonces hay un truco alternativo que es no ponerlos en el Group By y en cambio ponerles un MAX en la lista de selección (el MAX no hará nada si todos los datos son iguales). Pero no te lances a hacer este cambio "a ciegas", primero comprueba en el plan de ejecución si realmente lo necesitas.

    miércoles, 25 de julio de 2018 6:42
  • Soy nuevo en esto de usar el plan de ejecución, ya que en el poco tiempo que tengo manejando bases de datos no se me había presentado esta situación.

    Abrí el plan de ejecución y me algunos bloques tienen mucho porcentaje, pero mi pregunta es: en base a eso como puedo optimizarlo?


    JK

    miércoles, 25 de julio de 2018 16:46
  • No se ve si a la izquierda hay alguna otra cosa que tenga "peso", pero entre los bloques indicados hay dos con un "RID lookup" e "Index Seek" que son los más pesados. Dado que están entre medias de los "nested loops", significa que son debidos a los JOIN entre tablas. Y dado que hay cuatro tablas y cuatro nested loops, significa que los joins son poco eficientes y los está resolviendo mediante una comparación entre todos los elementos de cada par de tablas que se unen. Esto indicaría que no hay índices en las tablas que sean capaces de cubrir las condiciones que hay en los ON de los JOIN. En consecuencia, recomendación: Utilizar el DETA (database engine tuning advisor) y dejarle que te recomiende los índices adecuados. Si no lo has usado nunca, sigue algún tutorial porque la primera vez es complicado: hay que hacer una captura con el template de Tuning del Profiler y luego alimentar con ella al DETA.

    • Propuesto como respuesta Pablo RubioModerator viernes, 27 de julio de 2018 18:40
    • Marcado como respuesta JuanK1916 lunes, 13 de agosto de 2018 19:55
    miércoles, 25 de julio de 2018 17:30