Optimizando un SELECT que compara columnas de la misma tabla (2)

En este artículo:

Optimizando un SELECT que compara columnas de la misma tabla

vimos una técnica para optimizar los SELECT que comparan columnas de la misma tabla. La ventaja de esa técnica es que funcionará con cualquier motor SQL que utilicemos. Pero con Firebird tenemos además otra posibilidad, que es mejor que la anterior: usar índices de expresiones.

Nuestra tabla PRODUCTOS tiene la siguiente estructura:

Captura 1. Si haces clic en la imagen la verás más grande

Y podríamos crear un índice de expresión como el siguiente:

Listado 1.

CREATE INDEX IDX_PRODUCTOS ON PRODUCTOS COMPUTED BY (PRD_PREVTA - PRD_PRECTO);

Y nuestro SELECT tendría que ser así:

Listado 2.

SELECT
   *
FROM
   PRODUCTOS
WHERE
   PRD_PREVTA - PRD_PRECTO < 0

Donde la condición puesta en el WHERE tiene que ser igual que la expresión entre paréntesis en el Listado 1. Si no son iguales, el índice no será usado.

Si ahora miramos el rendimiento obtenido:

Captura 2. Si haces clic en la imagen la verás más grande

Veremos que efectivamente se ha usado el índice de expresión que creamos.

Las ventajas de usar un índice de expresión son:

1. No necesitas crear una columna adicional
2. No necesitas ejecutar un UPDATE para actualizar el contenido de la columna adicional
3. No necesitas escribir un trigger que se dedique a actualizar el contenido de la columna adicional

Conclusión:

Usar un índice de expresión nos facilita el trabajo cuando necesitamos poner en la cláusula WHERE una condición que compara columnas, pero como todo índice hacemos trabajar más al motor cada vez que se realiza un INSERT, un UPDATE, o un DELETE en la tabla, así que debemos sopesar las ventajas y las desventajas de utilizarlo.

Artículos relacionados:

Optimizando un SELECT que compara columnas de la misma tabla

El índice del blog Firebird21

El foro del blog Firebird21

 

 

 

Optimizando un SELECT que compara columnas de la misma tabla

En general, debemos tener a todas las columnas de todas nuestras tablas normalizadas. Eso es lo correcto y es lo recomendable. Sin embargo, hay ocasiones en que desnormalizar las columnas es conveniente.

Una de esas ocasiones es cuando debemos escribir un SELECT que en la cláusula WHERE compara el contenido de dos columnas. Veamos un ejemplo.

Tenemos la tabla PRODUCTOS con la siguiente estructura:

Captura 1. Si haces clic en la imagen la verás más grande

Y queremos saber si hay productos cuyo precio de venta es menor que su precio de costo, así que escribimos el siguiente SELECT.

Listado 1.

SELECT
   *
FROM
   PRODUCTOS
WHERE
   PRD_PREVTA < PRD_PRECTO

La consulta nos mostrará el resultado correcto, pero si analizamos su rendimiento, encontraremos que no ha usado un índice.

Captura 2. Si haces clic en la imagen la verás más grande

El problema es que no podemos tener un índice que pueda ser usado en casos como este. O sea, que no podemos tener índices para:

• Comparar dos columnas de la misma tabla por =
• Comparar dos columnas de la misma tabla por <
• Comparar dos columnas de la misma tabla por >
• Comparar dos columnas de la misma tabla por <=
• Comparar dos columnas de la misma tabla por >=
• Comparar dos columnas de la misma tabla por <>

Si la tabla tiene pocas filas, eso no es un problema, Firebird es muy rápido para devolver el resultado de los SELECT. Pero si la tabla tiene muchas filas, allí ya es otro tema.

¿Y cómo podemos hacer para mejorar la velocidad de nuestro SELECT?

La solución es crear una columna que contenga la diferencia entre las dos columnas que nos interesan. La estructura de la tabla PRODUCTOS quedaría entonces así:

Captura 3. Si haces clic en la imagen la verás más grande

Para mantener actualizada a la columna PRD_DIFERE podríamos escribir un trigger como el siguiente:

Listado 2.

CREATE TRIGGER PRODUCTOS_BIU FOR PRODUCTOS
   ACTIVE BEFORE
   INSERT OR
   UPDATE
   POSITION 1
AS
BEGIN

   NEW.PRD_DIFERE = NEW.PRD_PREVTA - NEW.PRD_PRECTO;

END;

Y para usar un índice entonces deberemos crearlo.

Listado 3.

CREATE INDEX IDX_PRODUCTOS ON PRODUCTOS(PRD_DIFERE);

Y si ahora escribimos el SELECT del Listado 1. modificado para que utilice a la columna PRD_DIFERE, tendríamos:

Listado 4.

SELECT
   *
FROM
   PRODUCTOS
WHERE
  PRD_DIFERE < 0

Queremos verificar si ahora se está usando un índice, así que miramos el rendimiento y encontramos:

Captura 4. Si haces clic en la imagen la verás más grande

Y comprobamos que sí, efectivamente ahora se usa un índice, y por lo tanto nuestro SELECT será mucho más rápido que antes.

Conclusión:

En general debemos tener a todas las columnas de todas nuestras tablas normalizadas, pero hay excepciones, como el caso mostrado en este artículo. Eso se debe a que el Firebird no utiliza índices cuando comparamos el contenido de una columna con el contenido de otra columna. La solución es crear una columna adicional que contendrá la diferencia entre los valores de las columnas que necesitamos comparar.

Desde luego que comparar precio de costo con precio de venta es sólo un ejemplo. También podemos comparar importe vendido contra importe cobrado, importe comprado contra importe pagado, etc.

Artículos relacionados:

El índice del blog Firebird21

El foro del blog Firebird21

EMS SQL Manager ha sido actualizado para Firebird 3

El programa administrador de bases de datos EMS SQL Manager desde el 9 de noviembre de 2016 ya ofrece soporte para las características y mejoras que fueron introducidas con Firebird 3.

Esto facilitará la migración de las bases de datos a Firebird 3 a aquellos que están acostumbrados a trabajar con este administrador gráfico.

Artículos relacionados:

El índice del blogFirebird21

El foro del blog Firebird21