Mejora de rendimiento de un slow changing dimension mediante programacion Tsql

01.03.2014 22:53

El componente Slow changing dimensión se utiliza para la actualización y el mantenimiento de datawarehouses a tavés  de la inserción selectiva de registros en las tablas de dimensiones de almacenamiento de datos.

La transformación Dimensión de variación lenta proporciona la siguiente funcionalidad para administrar dimensiones variables lentas:

  • Hacer coincidir filas entrantes con filas de la tabla de búsqueda para identificar filas nuevas y existentes.

  • Identificar las filas entrantes que contienen cambios cuando no se permiten cambios.

  • Identificar registros de miembros deducidos que requieren actualización.

  • Identificar filas entrantes que contienen cambios históricos que requieren la inserción de nuevos registros y la actualización de registros expirados.

  • Detección de filas entrantes que contienen cambios que requieren la actualización de los registros existentes, incluyendo los expirados. 

El componente puede ser descargado en:

https://dimensionmergescd.codeplex.com/

Aunque son muchas sus ventajas, su principal inconveniente es su lentitud. Veamos un ejemplo de ello y de como puede mejorarse con unas lineas de codigo tsql:

Creamos dos tablas, origen y destino, las cuales llenaremos con diez mil registros generados aleatoriamente los cuales constan de un identificador secuencial y de un campo de dos letras A y B combinadas aleatoriamente, el codigo que crea y genera las inserciones es el siguiente:

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 ,
   Name varchar(100)
);
 
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.iTarget') AND type in (N'U'))
    DROP TABLE dbo.iTarget;
     
CREATE TABLE dbo.iTarget
(
   ID INT ,
   Name varchar(100)
);

set nocount on
declare @count int=1
while @count <=10000
begin
INSERT INTO dbo.iSource (id,Name)
values (@count,char(ROUND(((67 - 65 -1) * RAND() + 65), 0))+char(ROUND(((67 - 65 -1) * RAND() + 65), 0)))
INSERT INTO dbo.iTarget (id,Name)
values (@count,char(ROUND(((67 - 65 -1) * RAND() + 65), 0))+char(ROUND(((67- 65 -1) * RAND() + 65), 0)))
set @count +=1
end
set nocount off

 

Veamos, una vez generado los registros que  no coinciden entre ambas tablas y los que si lo hacen:


         select * from iTarget
         intersect
         select * from iSource

el resultado de esta instruccion nos da, en este caso 2494 filas coincidentes y el resto, distintas.Nuestro objetivo es que, mediante una SCD podamos igualar la tabla origen y destino, cambiando solo los registros necesarios.

 

Vamos a crear un ETL en SSIS que nos realice la tarea:

Insertamos para ello en la entrada SCD las fuentes a comparar, asi como las correspondientes tareas de ordenacion para mejorar el rendimiento. La salida sera la tabla que deseamos actualizar.Utilizaremos una SCD del tipo 1

Lanzamos el proyecto y vemos su rendimiento:

 

La tarea a tardado 45 segundos.

Utilizando ahora una sencilla instruccion merge en sql:

MERGE dbo.iTarget AS target
    USING (
    SELECT ID, Name
    FROM dbo.iSource
     ) AS  source (ID, Name)
        ON (target.ID = source.ID)
        WHEN MATCHED AND target.Name <> source.Name
        THEN
            UPDATE SET Name = source.Name
     WHEN NOT MATCHED THEN
         INSERT (ID, Name)
         VALUES (source.ID, source.Name); 

y su tiempo de ejecución:

 


 Tiempos de ejecución de SQL Server:
   Tiempo de CPU = 47 ms, tiempo transcurrido = 42 ms.
 

42 milisegundos  contra 45 segundos, la diferencia merece ser tomada en consideración.

 

 

 

 

 

 

 

 

Etiquetas:

|

Volver