SSIS – Lookup con búsquedas por fechas

Buenas a todos, hace unas semanas he tratado de hacer búsquedas por fechas mediante el componente lookup de SSIS, obteniendo en un principio muy malos tiempos de rendimiento.

Planteamiento del problema.-

Estas pruebas fueron llevadas a cabo en un SQLServer 2008 R2. En un punto de mi proceso de carga realizo una búsqueda sobre una tabla que contiene muy pocos registros, sin embargo son muchos los datos que se cargan alrededor de 5 millones en la carga inicial. Esta búsqueda se realiza mediante campos de fecha, la fecha que me viene en el flujo la busco sobre dos fechas que tiene la tabla de referencia. La tabla de referencia no es más que una tabla con Fecha Inicio y una fecha de fin, además de un valor asignado a ese registro. La idea no es más que traerme el valor correspondiente a ese periodo. Estas situaciones se pueden dar de múltiples formas y en este caso se trata de un escenario de ventas de productos que se le aplica un porcentaje en base a la fecha de la venta. Para realizar la búsqueda, como tengo que buscar el valor que le corresponde en base a la fecha de alta y fecha de baja modifico la consulta del componente lookup de la siguiente forma:

select * from (select * from [Tabla]) [refTable]
where (? between [refTable].[FechaAlta] and [refTable].[FechaBaja]) or [refTable].FechaBaja is null

En la tabla de ventas el último periodo se entiende como el actual, por lo tanto no tiene una fecha de baja, sino que es nula.

Al modificar la consulta se pone en parcial cache.

El rendimiento de este componente es muy malo. Actualmente tiene 25Mb de cache configurada y he probado otras “técnicas” como incluir el nuevo componente Balanced Data Distribuidor (del cual les recomiendo el siguiente post de Víctor Sánchez http://blogs.solidq.com/BICorner/Post.aspx?ID=74&title=SSIS+2008+Paralelismo+con+Balanced+Data+Distributor), que ha mejorado mucho el rendimiento, pero aun así para 5 millones de filas tarda en torno a 2 horas y pico.

Este tiempo me parece excesivo porque el ETL aumenta de 10 minutos de ejecución sin esa búsqueda a 2 horas y pico con la misma.

Solución.-

Después de buscar y probar diversas formas abrí una consulta en el foro de SSIS de la msdn: http://social.msdn.microsoft.com/Forums/es-ES/ssises/thread/644de7d2-f670-441a-bcaf-784fe24fc510, donde Miguel Egea, un auténtico crack me propuso la utilización de las Common Table Expressions (CTE) para darle un mejor rendimiento a este componente ante esta problemática.

De esa forma plantee la consulta del componente lookup de la siguiente forma:

Consulta:

with miconsulta as
(
select fechaalta, fechabaja, Porcentaje, fechaalta fecha from TablaReferencia
union all
select fechaalta, fechabaja, Porcentaje, fecha+1 from miconsulta where fecha<fechabaja
)
select * from miconsulta order by 4 option(maxrecursion 0)

No olvidar que hay que poner el full cache en el componente lookup.

De esta forma se consigue aumentar el número de registros sobre los que buscar, para así hacer una búsqueda sin utilizar la consulta modificada (que puse en la explicación) si no por el contrario hacerla directamente y al tener todos los días podré buscar directamente, consiguiendo una gran mejora en el rendimiento del componente.

Conclusión.-

Antes utilizando la consulta con el between: 02:02:59, es decir alrededor de 2 horas…

Ahora utilizando la técnica propuesta: 00:01:50, minuto y medio… Hay que tener en cuenta que el ETL también hace uso del Balanced Data Distribuidor.

 

Espero que les pueda ayudar… 🙂

Un comentario en “SSIS – Lookup con búsquedas por fechas”

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *