none
Optimización de consulta RRS feed

  • Pregunta

  • Hola compañeros,

    Tengo la siguiente consulta donde muestro cuantas órdenes se realizan en cada mes por año.

       SELECT YEAR(OrderDate) AS [Año]
              ,SUM(CASE WHEN DATEPART(MM, OrderDate) = 1 THEN 1 ELSE 0 END) AS [Enero]
    	  ,SUM(CASE WHEN DATEPART(MM, OrderDate) = 2 THEN 1 ELSE 0 END) AS [Febrero]
    	  ,SUM(CASE WHEN DATEPART(MM, OrderDate) = 3 THEN 1 ELSE 0 END) AS [Marzo]
    	  ,SUM(CASE WHEN DATEPART(MM, OrderDate) = 4 THEN 1 ELSE 0 END) AS [Abril]
    	  ,SUM(CASE WHEN DATEPART(MM, OrderDate) = 5 THEN 1 ELSE 0 END) AS [Mayo]
    	  ,SUM(CASE WHEN DATEPART(MM, OrderDate) = 6 THEN 1 ELSE 0 END) AS [Junio]
    	  ,SUM(CASE WHEN DATEPART(MM, OrderDate) = 7 THEN 1 ELSE 0 END) AS [Julio]
    	  ,SUM(CASE WHEN DATEPART(MM, OrderDate) = 8 THEN 1 ELSE 0 END) AS [Agosto]
    	  ,SUM(CASE WHEN DATEPART(MM, OrderDate) = 9 THEN 1 ELSE 0 END) AS [Setiembre]
    	  ,SUM(CASE WHEN DATEPART(MM, OrderDate) = 10 THEN 1 ELSE 0 END) AS [Octubre]
    	  ,SUM(CASE WHEN DATEPART(MM, OrderDate) = 11 THEN 1 ELSE 0 END) AS [Noviembre]
    	  ,SUM(CASE WHEN DATEPART(MM, OrderDate) = 12 THEN 1 ELSE 0 END) AS [Diciembre]
    	  ,COUNT(OrderDate) AS [Total]
       FROM Orders
       GROUP BY YEAR(OrderDate)
       GO

    Lo cual me da como resultado :

    Mi pregunta es la consulta se puede optimizar, es decir existe alguna forma de mejorarla ? (en rendimiento o performance) o tal vez exista otra manera de hacerlo ?

    Saludos y gracias por la ayuda que me puedan brindar.


    JC NaupaCrispín
    Lima - Perú

    La magia no existe, la programación SI



    martes, 13 de septiembre de 2016 0:37

Respuestas

  • la otra manera que me parece seria que hicieras una tabla de fechas con todas las fechas posibles en el año y hagas un join contra esa pero no sabría de buenas a primeras la diferencia de performance, pero al no haber conversiones casi seria lo mismo.
    martes, 13 de septiembre de 2016 2:26
  • Joel C. Naupa Crispín,

    El caso que presentas se conoce como CrossTab Query que básicamente es transponer los valores de fila a columnas y para afrontar este caso yo sólo conozco dos métodos:

    1. Aplicando la instrucción CASE a cada valor utilizando una función de agregado para resumir el valor mostrado (lo que presentas).
    2. Haciendo uso del operador relacional PIVOT.

    En lo personal prefiero transponer filas a columnas -en tanto pueda- mediante instrucciones CASE, ya verás casos en que el número de columnas no se conocen a priori y ahí si que echo mano al operador PIVOT apoyándome de sql dinámico.

    Respecto al rendimiento, operas funciones de fecha sobre la columna 'OrderDate' y en caso estar indexada (es lo esperado) dichas funciones la invalidan. Una salida a dicho problema es agregar a la tabla dos columnas calculadas (Anio, Mes) con el atributo PERSISTED e indexarlas, de modo que la consulta se da sobre valores atómicos e indexados.


    Espero que la información proporcionada te haya sido de utilidad, quedo atento a tus comentarios.
    martes, 13 de septiembre de 2016 4:30
  • Buen dia,

    Por lo que veo y sin conocer mucho el ambiente, me parece que a lo mejor te combiene armar algun de modelo de DW con tablas de hechos y dimensiones, que vayas cargado datos a estas en forma periodica, el cual te permita hacer consultas directas y sin necesidad de sumarizar con case.

    Saludos


    Carlos Ignacio Aguero. DBA SQL Server. Toda mi respeto al pueblo Peruano por la ayuda prestada en la guerra de Malvinas.

    martes, 13 de septiembre de 2016 11:53
  • Joel,

    No pierdas tiempo con el PIVOT pues a la final es un metodo reducido de hacer la transposicion de filas a columnas que usa el mismo principio que usastes.

    - Agrupar por año (GROUP BY)

    - Regar valores por meses (expresion CASE)

    - Agregar (SUM / MIN / etc.)

    El plan puede que cambie un poquito, sobre todo en la forma que se riegan los valores, pero la diferencia no impactara el desempenio.

    SELECT 
    	YEAR(OrderDate) AS [Año]
    	,SUM(CASE WHEN DATEPART(MM, OrderDate) = 1 THEN 1 ELSE 0 END) AS [Enero]
    	,SUM(CASE WHEN DATEPART(MM, OrderDate) = 2 THEN 1 ELSE 0 END) AS [Febrero]
    	,SUM(CASE WHEN DATEPART(MM, OrderDate) = 3 THEN 1 ELSE 0 END) AS [Marzo]
    	,SUM(CASE WHEN DATEPART(MM, OrderDate) = 4 THEN 1 ELSE 0 END) AS [Abril]
    	,SUM(CASE WHEN DATEPART(MM, OrderDate) = 5 THEN 1 ELSE 0 END) AS [Mayo]
    	,SUM(CASE WHEN DATEPART(MM, OrderDate) = 6 THEN 1 ELSE 0 END) AS [Junio]
    	,SUM(CASE WHEN DATEPART(MM, OrderDate) = 7 THEN 1 ELSE 0 END) AS [Julio]
    	,SUM(CASE WHEN DATEPART(MM, OrderDate) = 8 THEN 1 ELSE 0 END) AS [Agosto]
    	,SUM(CASE WHEN DATEPART(MM, OrderDate) = 9 THEN 1 ELSE 0 END) AS [Setiembre]
    	,SUM(CASE WHEN DATEPART(MM, OrderDate) = 10 THEN 1 ELSE 0 END) AS [Octubre]
    	,SUM(CASE WHEN DATEPART(MM, OrderDate) = 11 THEN 1 ELSE 0 END) AS [Noviembre]
    	,SUM(CASE WHEN DATEPART(MM, OrderDate) = 12 THEN 1 ELSE 0 END) AS [Diciembre]
    	,COUNT(*) AS [Total]
    FROM
    	dbo.Orders
    GROUP BY 
    	YEAR(OrderDate)
    GO
    SELECT 
    	P.YYYYY AS Año,
        P.[1] AS Enero,
        P.[2] AS Febrero,
        P.[3] AS Marzo,
        P.[4] AS Abril,
        P.[5] AS Mayo,
        P.[6] AS Junio,
        P.[7] AS Julio,
        P.[8] AS Agosto,
        P.[9] AS Septiembre,
        P.[10] AS Octubre,
        P.[11] AS Noviembre,
        P.[12] AS Diciembre,
        P.[1] + 
        P.[2] + 
        P.[3] + 
        P.[4] + 
        P.[5] + 
        P.[6] + 
        P.[7] + 
        P.[8] + 
        P.[9] + 
        P.[10] + 
        P.[11] + 
        P.[12] AS Total
    FROM
    	(
    	SELECT YEAR(OrderDate) AS YYYYY, MONTH(OrderDate) AS MM, 1 AS dummyCol 
    	FROM dbo.Orders
    	) AS T0
    	PIVOT
    	(
    	SUM(dummyCol)
    	FOR MM IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])
    	) AS P
    GO

    Para que ambas salidas sean la misma deberas usar ISNULL/COALESCE por cada columna que sale del PIVOT.

    ISNULL(P.[1], 0) AS Enero, ...


    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas

    martes, 13 de septiembre de 2016 18:57

Todas las respuestas

  • la otra manera que me parece seria que hicieras una tabla de fechas con todas las fechas posibles en el año y hagas un join contra esa pero no sabría de buenas a primeras la diferencia de performance, pero al no haber conversiones casi seria lo mismo.
    martes, 13 de septiembre de 2016 2:26
  • Joel C. Naupa Crispín,

    El caso que presentas se conoce como CrossTab Query que básicamente es transponer los valores de fila a columnas y para afrontar este caso yo sólo conozco dos métodos:

    1. Aplicando la instrucción CASE a cada valor utilizando una función de agregado para resumir el valor mostrado (lo que presentas).
    2. Haciendo uso del operador relacional PIVOT.

    En lo personal prefiero transponer filas a columnas -en tanto pueda- mediante instrucciones CASE, ya verás casos en que el número de columnas no se conocen a priori y ahí si que echo mano al operador PIVOT apoyándome de sql dinámico.

    Respecto al rendimiento, operas funciones de fecha sobre la columna 'OrderDate' y en caso estar indexada (es lo esperado) dichas funciones la invalidan. Una salida a dicho problema es agregar a la tabla dos columnas calculadas (Anio, Mes) con el atributo PERSISTED e indexarlas, de modo que la consulta se da sobre valores atómicos e indexados.


    Espero que la información proporcionada te haya sido de utilidad, quedo atento a tus comentarios.
    martes, 13 de septiembre de 2016 4:30
  • Buen dia,

    Por lo que veo y sin conocer mucho el ambiente, me parece que a lo mejor te combiene armar algun de modelo de DW con tablas de hechos y dimensiones, que vayas cargado datos a estas en forma periodica, el cual te permita hacer consultas directas y sin necesidad de sumarizar con case.

    Saludos


    Carlos Ignacio Aguero. DBA SQL Server. Toda mi respeto al pueblo Peruano por la ayuda prestada en la guerra de Malvinas.

    martes, 13 de septiembre de 2016 11:53
  • Enrique AAWillams Morales y Carlos Ignacio Cba

    Gracias por sus respuestas, las tomaré en cuenta.

    [-] Respondiendo a Willams :

    Sí también me encontré en el web con PIVOT, pero esperaba no usarla (ya que parece más difícil) y claro pensaba que usando el CASE tantas veces era tal vez una mala práctica, pero veo que tiene nombre esa técnica.

    Y bueno trataré de hacerlo con Pivot.

    [-] Respondiendo a Carlos :

    Ahí si me mataste (jeje) si el PIVOT me parecía complicado, usando el DataWarehouse tendría que crear todo desde cero, lo menciono porque no estoy enfocado como DBA al 100%, ya que sólo quiero mejorar y/o saber cuándo se puede optimizar una consulta.

    Uso la BD Northwind para cuestiones de práctica como la consulta presente.

    Saludos.


    JC NaupaCrispín
    Lima - Perú

    La magia no existe, la programación SI

    • Propuesto como respuesta HunchbackMVP martes, 13 de septiembre de 2016 18:52
    • Votado como útil HunchbackMVP martes, 13 de septiembre de 2016 18:53
    martes, 13 de septiembre de 2016 17:32
  • Joel,

    No pierdas tiempo con el PIVOT pues a la final es un metodo reducido de hacer la transposicion de filas a columnas que usa el mismo principio que usastes.

    - Agrupar por año (GROUP BY)

    - Regar valores por meses (expresion CASE)

    - Agregar (SUM / MIN / etc.)

    El plan puede que cambie un poquito, sobre todo en la forma que se riegan los valores, pero la diferencia no impactara el desempenio.

    SELECT 
    	YEAR(OrderDate) AS [Año]
    	,SUM(CASE WHEN DATEPART(MM, OrderDate) = 1 THEN 1 ELSE 0 END) AS [Enero]
    	,SUM(CASE WHEN DATEPART(MM, OrderDate) = 2 THEN 1 ELSE 0 END) AS [Febrero]
    	,SUM(CASE WHEN DATEPART(MM, OrderDate) = 3 THEN 1 ELSE 0 END) AS [Marzo]
    	,SUM(CASE WHEN DATEPART(MM, OrderDate) = 4 THEN 1 ELSE 0 END) AS [Abril]
    	,SUM(CASE WHEN DATEPART(MM, OrderDate) = 5 THEN 1 ELSE 0 END) AS [Mayo]
    	,SUM(CASE WHEN DATEPART(MM, OrderDate) = 6 THEN 1 ELSE 0 END) AS [Junio]
    	,SUM(CASE WHEN DATEPART(MM, OrderDate) = 7 THEN 1 ELSE 0 END) AS [Julio]
    	,SUM(CASE WHEN DATEPART(MM, OrderDate) = 8 THEN 1 ELSE 0 END) AS [Agosto]
    	,SUM(CASE WHEN DATEPART(MM, OrderDate) = 9 THEN 1 ELSE 0 END) AS [Setiembre]
    	,SUM(CASE WHEN DATEPART(MM, OrderDate) = 10 THEN 1 ELSE 0 END) AS [Octubre]
    	,SUM(CASE WHEN DATEPART(MM, OrderDate) = 11 THEN 1 ELSE 0 END) AS [Noviembre]
    	,SUM(CASE WHEN DATEPART(MM, OrderDate) = 12 THEN 1 ELSE 0 END) AS [Diciembre]
    	,COUNT(*) AS [Total]
    FROM
    	dbo.Orders
    GROUP BY 
    	YEAR(OrderDate)
    GO
    SELECT 
    	P.YYYYY AS Año,
        P.[1] AS Enero,
        P.[2] AS Febrero,
        P.[3] AS Marzo,
        P.[4] AS Abril,
        P.[5] AS Mayo,
        P.[6] AS Junio,
        P.[7] AS Julio,
        P.[8] AS Agosto,
        P.[9] AS Septiembre,
        P.[10] AS Octubre,
        P.[11] AS Noviembre,
        P.[12] AS Diciembre,
        P.[1] + 
        P.[2] + 
        P.[3] + 
        P.[4] + 
        P.[5] + 
        P.[6] + 
        P.[7] + 
        P.[8] + 
        P.[9] + 
        P.[10] + 
        P.[11] + 
        P.[12] AS Total
    FROM
    	(
    	SELECT YEAR(OrderDate) AS YYYYY, MONTH(OrderDate) AS MM, 1 AS dummyCol 
    	FROM dbo.Orders
    	) AS T0
    	PIVOT
    	(
    	SUM(dummyCol)
    	FOR MM IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])
    	) AS P
    GO

    Para que ambas salidas sean la misma deberas usar ISNULL/COALESCE por cada columna que sale del PIVOT.

    ISNULL(P.[1], 0) AS Enero, ...


    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas

    martes, 13 de septiembre de 2016 18:57
  • Hola Hunchback,

    Gracias por el ejemplo, funciona correctamente.

    Estaba haciendo pruebas con Pivot pero mi ejemplo no se asemeja ni al 30% de lo que haz mostrado, bueno solo toca estudiarla detalladamente.

    Saludos.


    JC NaupaCrispín
    Lima - Perú

    La magia no existe, la programación SI

    miércoles, 14 de septiembre de 2016 2:47