Skip to content

Procedimiento almacenado recursivo sin cursores

June 22, 2012
Actualmente estoy inmerso en un proyecto que me está suponiendo un esfuerzo extra para poder ir implementando la funcionalidades que se me van pidiendo. Sin duda la parte más compleja de este proyecto ha sido el diseño e implementación de una función recursiva, para el cálculo de las cantidades necesarias de cada artículo i sus componentes, que sería algo parecido a recetas, a partir de unas unidades necesarias según producción prevista.
La complejidad ha venido porqué teniendo dos tablas, una de artículos, y otra de componentes, un artículo bien también puede ser un componente, y un componente un artículo. Los artículos pasados iterativamente como parámetro a la función recursiva, son los artículos raíz del árbol, se miran sus componentes, y por cada componente se vuelve a llamar a la función (dentro de sí misma), de forma recursiva. Pero a cada paso tenemos que ir calculando los quilos que van quedando del artículo que estamos mirando, según un peso neto que consta en la tabla de artículos, y una cantidad en la de componentes, teniendo en cuenta que un árbol puede contener un mismo componente en distintas ramas, informado distintamente en la tabla de componentes. Por ejemplo, podemos tener sal en distintas ramas, en cantidades distintas. En la tabla componentes tenemos un campo que informa de quien es el padre, que apunta a la tabla de artículos.

Bien, cuando el usuario encargado, modifica cualquier cantidad de unidades de la producción prevista, tenemos que hacer todo el recálculo de nuevo, ya que el que nos interesa es saber la cantidad necesaria de cada producto, y al repetirse en distintos recetas, al hacer solo una modificación, ya tenemos que rehacer todos los cálculos.

El problema es el tiempo de ejecución de estos cálculos, y con código desde VB, llamando a SELECTS y UPDATES en la base de datos, desde código, el mínimo de tiempo de ejecución conseguido ha sido de 15 segundos, utilizando tablas temporales en memoria local.

Seguidamente mediante un PROCEDURE y trabajando con CURSORES consigo reducir el tiempo de cálculo a 10 segundos trabajando siempre sobre tablas temporales. Pero esto no sirve, ya que el usuario necesita ver los cambios a tiempo real, casi instantáneamente. 

A continuación se hace lo mismo SIN cursores, con un WHILE, y podemos bajar ya a los 4-5 segundos a reseguir los árboles de todos los artículos raíz. No obstante, aun con algún error de duplicado, que se tiene que encontrar ya que la recursiva itera más de la cuenta. Esta opción me está resultando un poco costosa y no acaba de funcionar correctamente, pues en el caso que me trae el árbol de un artículo, se queda colgado sin salir del procedimiento por la condición WHILE @current<= @end. Y en caso de  WHILE @current< @end, no se muestra todo el árbol. Seguro que tengo algún error de programación, pero no logro encontrarlo.

La siguiente prueba es trabajar con la versión 2008 de SQL Server, que permite pasar tablas enteras como parámetros, o también se puede iterar recursivamente con CTE usando WITH. Este fin de semana me dedicaré a resolver este asunto.

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: