Optimización de consulta

Question

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

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

Enrique AA, Willams 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

Answer 5

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

Answer 6

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