Código eficiente vs código ineficiente.
Frecuentemente los directivos de las empresas piden reportes de: gastos, ventas y compras, donde quieren que los datos se muestren en columnas, ya sea por meses o por años. Lo que hacemos incialmente los principiantes es utilizar subconsultas dentro del código de una consulta general; donde cada subconsulta traiga el dato que resulta para el año o mes indicado. Con esta forma de hacer el Query obtenemos el resultado que nos piden y hasta nos sentimos contentos por el tiempo invertido y la gran cantidad de lineas escritas en SQL.
Sin embargo hoy me di cuenta de que por esa via hacemos trabajar mucho mas al SQL Server y esa es la causa de que la consulta tarde más en mostrar el resultado. (sugiero que lean el siguiente artículo: h_tps://www.sqlshack.com/es/planes-de-ejecucion-de-consultas-sql-server-viendo-los-planes/).
Podria decirse que la primera via no es la mas eficiente y debe existir otra via, donde para transponer columnas en un reporte sea mas rapida.
Por esa razon prove a escribir el mismo Query (me refiero a iguales campos y resultados) pero esta vez usando la Funcion PIVOT del SQL Server. La que habia estudiado hace unas a atrás.
Que creen, los resultados fueron asombrosos porque en la segunda via (Consulta con PIVOT) escribí la mitad del codigo y el tiempo ejecución de la consulta fue muchisimo menor. Es por eso que a continuacion les muestro las consultas comparadas.
Ejemplo 1. Se muestra un Query en SQL poco eficiente.
– Muestra el total vendido (con IVA incluido) por clientes y por año, se tienen en cuenta las notas de créditos
– El reporte está basado en las transacciones registradas en el libro mayor (OJDT y JDT1)
– Se utilizan subconsultas para mostrar los totales por año
– Aparentemente está todo bien, pero su ejecución es muy tardada (el resultada demora 37 s)
SELECT
T0.CardCode 'Código',
T0.CardName 'Nombre_Cliente',
(SELECT SUM(T2.Debit) - sum(T2.Credit)
FROM JDT1 T2 INNER JOIN OJDT T4 ON T4.TransID = T2.TransID AND Year(T4.Refdate) = 2015
WHERE T2.ShortName = T0.CardCode AND T2.TransType in ('13','14')) AS 'Ventas 2015',
(SELECT SUM(T2.Debit) - sum(T2.Credit)
FROM JDT1 T2 INNER JOIN OJDT T4 ON T4.TransID = T2.TransID AND Year(T4.Refdate) = 2016
WHERE T2.ShortName = T0.CardCode AND T2.TransType in ('13','14')) AS 'Ventas 2016',
(SELECT SUM(T2.Debit) - sum(T2.Credit)
FROM JDT1 T2 INNER JOIN OJDT T4 ON T4.TransID = T2.TransID AND Year(T4.Refdate) = 2017
WHERE T2.ShortName = T0.CardCode AND T2.TransType in ('13','14')) AS 'Ventas 2017',
(SELECT SUM(T2.Debit) - sum(T2.Credit) FROM JDT1 T2
INNER JOIN OJDT T4 ON T4.TransID = T2.TransID AND Year(T4.Refdate) = 2018
WHERE T2.ShortName = T0.CardCode AND T2.TransType in ('13','14')) AS 'Sales 2018'
FROM OCRD T0
WHERE T0.CardType = 'C' and T0.validfor='Y'
ORDER By T0.CardCode
Ejemplo 2. Se muestra el mismo Query de manera más eficiente.
– Se muestra el mismo resultado que en el Query anterior pero se usan menos líneas de código SQL y la ejecución toma mucho menos tiempo (el resultada demora 1.5 segundos)
– Elaborado por: Eric Álvarez Consultores IBIZ S.A. de C.V. Fecha: 23/04/2018
With Venta (Código, Nombre_Cliente, Año, Venta) as
(
SELECT
T0.[ShortName],
T2.[CardName],
year (T1.[RefDate]),
SUM(T0.[Debit]) - SUM(T0.[Credit])
FROM JDT1 T0
INNER JOIN OJDT T1 ON T0.[TransId] = T1.[TransId]
INNER JOIN OCRD T2 ON T0.[ShortName]=T2.[CardCode]
WHERE T1.[TransType] IN (13,14) and Year (T1.[RefDate] ) between 2015 and 2018
GROUP BY T0.[ShortName], T2.[CardName], T1.[RefDate]
)
SELECT * FROM Venta
PIVOT (SUM (Venta) For Año IN ([2015],[2016],[2017],[2018]) ) PIV
Order By Código
Considero que tambien es Bueno compartir con los demas experiencias donde aprendemos algo nuevo.
es posible?
