Comparativa de rendimiento en funciones de agregado para datawarehouses II
Una vez tenemos la tabla vamos a realizar una query con las window function, que nos permiten agrupar agregados por distintos campos, a diferencia
del group by:
Probaremos una query que nos devuelva todos los registros cuya cuenta sea la que suma el valor máximo de todas y que nos devuelva para cada registro un
campo con los valores agregados de suma y cuenta de registros por grupo:
select
id,idsum,idaccount,idcount,idsum2,percentotal,total from (select id,IDSUM, Idaccount , cast(sum(idsum)over(partition by idaccount) as numeric(18,4))/(sum(idsum)over())as percentotal,count(idsum)over(partition by idaccount) as idcount,sum(idsum)over(partition by idaccount)as idsum2,sum(idsum)over()as total from iSource)as detalle
where
idsum2=(select max(idsum) from (select idaccount , cast(sum(idsum)over(partition by idaccount) as numeric(18,4))/(sum(idsum)over())as percentotal,count(idsum)over(partition by idaccount) as idcount,sum(idsum)over(partition by idaccount)as idsum from iSource)as d)
La query nos devuelve el resultado de 10306 registros sobre un millon en un tiempo
de casi 13 segundos:
Tabla
'iSource'. Recuento de exámenes 10, lecturas lógicas 7562, lecturas físicas 0, lecturas anticipadas 0, lecturas lógicas de LOB 0, lecturas físicas de LOB 0,
lecturas anticipadas de LOB 0.
Tabla
'Worktable'. Recuento de exámenes 27, lecturas lógicas 7027318, lecturas físicas 0, lecturas anticipadas 0, lecturas lógicas de LOB 0, lecturas físicas
de LOB 0, lecturas anticipadas de LOB 0.
(1 filas afectadas)
Tiempos de ejecución de SQL Server:
Tiempo de CPU = 12916 ms, tiempo transcurrido = 11144 ms.
Como vemos a realizado más de 7 millones de lecturas lógicas.
Probemos ahora con group by, sentencia que no nos permite agrupar por distintos campos simultáneamente y que nos obliga a hacer distintas select
agrupadas para cada agregado y joins con la tabla principal:
select
id,isource.IDSUM,d3.idaccount,IDCOUNT,D3.idsum ,percentotal,total from isource
inner join (
select
* from (select idaccount,count(idsum)as idcount,sum(idsum) as idsum,cast(sum(idsum) as numeric(18,4))/(select sum(idsum) from isource) as percentotal ,(select sum(idsum) from isource) as total from isource
group by (idaccount))as d
where
idsum=(select max(idsum) from (select idaccount,count(idsum)as idcount,sum(idsum) as idsum,cast(sum(idsum) as numeric(18,4))/(select sum(idsum) from isource) as total from isource
group by (idaccount))as d2)
) as d3
on isource.IDACCOUNT=d3.IDACCOUNT.
Bien, esta sentencia devuelve los mismos resultados que la anterior pero en apenas un segundo!
Tabla 'iSource'. Recuento de exámenes 16, lecturas lógicas 10324, lecturas físicas 0, lecturas anticipadas 0, lecturas lógicas de LOB 0, lecturas físicas
de LOB 0, lecturas anticipadas de LOB 0.
Tabla 'Worktable'. Recuento de exámenes 0, lecturas lógicas 0, lecturas físicas 0, lecturas anticipadas 0, lecturas lógicas de LOB 0, lecturas físicas
de LOB 0, lecturas anticipadas de LOB 0.
(1 filas afectadas)
Tiempos de ejecución de SQL Server:
Tiempo de CPU = 1200 ms, tiempo transcurrido = 697 ms.
Como vemos no ha realizado más que 10324 lecturas lógicas en contraste con los mas de 7 millones de la anterior.
Queda pues de manifiesto que la segunda opción es la de mejor rendimiento.