Bucles y problemas de rendimiento (performance)

El lenguaje PL/SQL permite la contrucción de bucles, esta funcionalidad es, sin duda, extremadamente útil. No obstante, utilizar bucles puede causar problemas de rendimiento en nuestras rutinas, aunque ciertamente estos problemas son fáciles de determinar y corregir.

Imaginemos que una rutina PL/SQL contiene una sentencia select y los registros que devuelve son utilizados para actualizar otra tabla dentro de un bucle. Por ejemplo:

FOR bucle1 IN (
  SELECT articulo
  FROM articulos
  WHERE descripcion = '%zapato%'
) LOOP
  UPDATE precios
  SET precio = precio * 1.05
  WHERE articulo = bucle1.articulo;
END LOOP;

Es decir, queremos subir un 5% el precio de todos los artículos en cuya descripción aparece la palabra "zapato". Ahora supongamos que en la tabla PRECIOS no existe un índice sobre la columna ARTICULO. Esto significa que la tabla PRECIOS va a ser escaneada en su totalidad tantas veces como artículos haya en los que su descripción incluya la palabra zapato haya. Una verdadera pérdida de tiempo y realmente perjudicial para el rendimiento de la sentencia que puede llegar a ser caótico si la tabla PRECIOS tiene muchos registros.

Afortunadamente disponemos de tres maneras para corregir el problema:

1. Crear un índice

Obviamente podemos crear un índice para la tabla PRECIOS sobre la columna ARTICULO. De esta manera en cada ejecución del bucle actualizaremos la tabla PRECIOS utlizando dicho índice en lugar de escaneando en su totalidad dicha tabla. Sin duda el rendimiento de nuestra sentencia mejorará notablemente.

No obstante crear un índice no es la mejor forma de corregir un problema como este. Primero se deben considerar un par de cuestiones:

a) Siempre que se crea un nuevo índice existe el riesgo de afectar negativamente a algún otro elemento del sistema. Las bases de datos de hoy en día no son pequeños núcleos de información sino que son grandes focos de datos integrados y sus piezas no operan de forma independiente. La creación de un nuevo índice cambiará los planes de ejecución de muchas sentencias que acceden a la tabla sobre la que se creó el nuevo índice. Por tanto, siempre que se añade un nuevo índice hay que estar preparado para poder deshacer el cambio ya que lo que hicimos para mejorar una cosa puede causar que otras dejen de funcionar como lo hacían anteriormente. Por otro lado, cualquier nuevo índice debe ser evaluado por un DBA, no hay que lanzarse al vacío solo.

b) Si lo que queremos conseguir es cambiar el plan de ejecución de la sentencia que hay dentro del bucle, entonces debemos preguntarnos si verdaderamente el plan de ejecución actual es verdaderamente malo. Las últimas versiones del optimizador basado en costes de Oracle son muy buenas, quizás no perfectas, pero si las estadísticas de tablas e índices han sido recogidas, entonces probablemente el optimizador esté generando buenos planes de ejecución. El plan de ejecución de la sentencia "update" de nuestro ejemplo también podría ser correcto y el problema podría residir en el hecho de que dicha sentencia es ejecutada repetidas veces.

Esto nos lleva a tener que pensar en otra posible solución.

2. Prescindir del bucle

Lo que tenemos que hacer es prescindir del bucle para conseguir utilizar la habilidad del optimizador sin que esto afecte a otros objetos de la base de datos. Así que por qué no cambiar nuestro código como sigue:

UPDATE precios
SET precio = precio * 1.05
WHERE articulo IN
  (
  SELECT articulo
  FROM articulos
  WHERE descripcion = '%zapato%'
  );

Esta sentencia va a hacer exáctamente lo mismo que el código original pero el bucle está dentro de la sentencia "update" mediante la inclusión de la sentencia "select" dentro de la claúsula "where". El hecho de que no exista un índice sobre la columna ARTICULO es ahora menos importante. El optimizador de Oracle puede considerar ahora la naturaleza de la sentencia "select" en su plan de ejecución. Si hay suficientes registros en PRECIOS que requieran ser actualizados entonces un escaneo completo de la tabla puede ser la solución más conveniente.

Obviamente no siempre el código que hay dentro de un bucle pueder ser tan fácilmente reemplazado por una única sentencia, pero siempre se puede dar con alguna solución de este tipo que será más o menos complicada, pero que siempre lo será menos que el tener que probar todos los módulos que pueden verse afectados por añadir un nuevo índice. Ummmm... me pregunto si existen muchas empresas que vuelven a probar su código cuando se crea un nuevo índice.

3. Utilizar una tabla intermedia.

Dentro de un bucle pueden existir gran número de sentencias SQL y puede resultar que no nos interese implementar una solución como la propuesta en el punto anterior. A veces puede resultar interesante utilizar una vieja técnica; la tabla intermedia.

CREATE TABLE intermedia (articulo VARCHAR(20));

INSERT INTO intermedia
SELECT articulo
FROM articulos
WHERE descripcion = '%zapato%';

UPDATE precios
SET precio = precio * 1.05
WHERE articulo IN
  (
  SELECT articulo
  FROM intermedia
  );

Utilizar una tabla intermedia para almacenar los registros que necesitan ser actualizados nos permite utilizar esta información en cualquier lugar del código. Mucha gente piensa que utilizar tablas intermedias nunca es una buena opción, pero las tablas intermedias son, ciertamente, unos mecanismos muy efectivos cuando son utilizadas de un forma correcta.

Recapitulando, las soluciones que he mencionado para tratar los problemas de rendimiento causados por la utilización de bucles son a) hacer algo con los índices de manera que se altere el plan de ejecución de cada sentencia incluida en el bucle y esperar que la ejecución del bucle sea más rápida (solución no recomendada), o b) eliminar el bucle mediante la inclusión del código del bucle directamente dentro de la sentencia SQL principal, o indirectamente vía tabla intermedia.

¿Cuánto podemos esoerar que mejore el rendimiento de nuestra aplicación después de los cambios? Naturalmente esto dependerá de cuan malo sea el rendimiento de la rutina. En mi experiencia diría que se puede esperar que el rendimiento sea entre 10 y 100 veces mejor. Trabajos que tarden 10 horas pueden llegar a tardar 1 hora, o mejor aún, tan sólo 10 minutos. Estos órdenes de magnitud son importantes pero no debemos esperar menos de este tipo de cambios ya que nuestra sentencia SQL se ejecutará sólo una vez y no N veces que es lo que ocurre cuando utilizamos un bucle.