Comparativa de rendimiento en funciones de agregado para datawarehouses I
Es frecuente, cuando estamos preparando un datawarehose, que nos interese guardar en un archivo los datos de una gran variedad de campos jerárquicos agrupados, tales como count, sumas, promedios etc, para cada registro de una tabla.
Tabla 1.
Supongamos, segun el ejemplo anterior, que tenemos una tabla con 1 millon de registros, en la que queremos conseguir unos resultados agrupados en campos para cada fila. La tabla de origen es de la siguiente forma:
Tabla 2
Tenemos 3 campos, un identifcador ID, un identificador de cuenta, en que cada registro es asignado a una cuenta, y donde existen 100 idaccount posibles. Por ultimo tenemos un campo valor IDSUM donde guardamos los valores que queremos sumar o contar. En este caso almacenamos valores de uno a 10000.
Nuestro objetivo es obtener una tabla a partir de la Tabla 2, con los valores agrupados para cada registro como vemos en la Tabla 1
La nueva tabla, que nos servirá de base para hacer mas calculos en el datawarehouse, tendra la siguiente forma:
Tabla 3.
En esta tabla, obtenemos el id del registro, su valor IDSUM, la cuenta al que pertenece, cuantos registros hay en cada cuenta (idsum) el porcentaje sobre
el total que representan i el total.
Esta tabla no está normalizada, sino que nos ha de permitir tener todos los datos pre calculados, los cuales nos sirvan a su vez de base para los cálculos
finales que podemos realizar Reporting Services.en donde podemos a su vez agruparlos por fechas o criterios de otros campos
Queremos tener los datos pre calculados, porque nuestra base de datos tiene muchos registros y calcularlos en tiempo real puede ser muy costoso y nuestro
report final sería extremadamente lento.
Nuestro objetivo es pre calcularlos en una ETL que lanzamos diariamente, y que a su vez, ha de ir lo más rápido posible.
Vamos a preparar un modelo de ejemplo, donde probaremos el rendimiento con
window functions y con un group by:
Generamos primero una tabla con un millón de registros para realizar las pruebas sobre ella:
En esta tabla, obtenemos el id del registo, su valor IDSUM, la cuenta al que pertenece, cuantos registros hay en cada cuenta (idsum) el porcentaje sobre el total que representan i el total.
Esta tabla no está normalizada, sino que nos ha de permitir tener todos los datos precalculados, los cuales nos sirvan a su vez debase para los cálculos finales que podemos realizar en Reporting Services.
Queremos tener los datos precalculados, porque nuestra base de datos tiene muchos registros y calcularlos en tiempo real puede ser muy costoso y nuestro report final seria extremadamente lento.
Nuestro objetivo es precalcularlos en una ETL que lanzamos diariamente, y que a su vez, ha de ir lo más rápido posible.
SET
ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF
EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.iSource') AND type in (N'U'))
DROP TABLE dbo.iSource;
CREATE TABLE [dbo].[iSource](
[ID] [int] NULL,
[IDACCOUNT] [int] NULL,
[IDSUM] [bigint] NULL
)
ON [PRIMARY]
GO
set nocount on
declare @count int=1
while @count <=1000000
begin
insert
into [dbo].[iSource]
(
id,idaccount,idsum)
values
(@count,round(rand()*100,0),round(rand()*10000,0))
set @count+=1
end
set nocount off
Continua: