Ejemplo Nº 002 – Usando INNER JOIN eficientemente

Como seguramente ya sabes, INNER JOIN es el join que se usa por defecto en SQL y por lo tanto en Firebird. La palabra INNER es opcional, si lo deseas puedes escribir solamente JOIN y eso le indica al Firebird que deseas hacer un INNER JOIN.

Cuando dos tablas están relacionadas mediante un INNER JOIN la consulta devolverá las filas que comparten valores comunes en ambas tablas.

Sin embargo, no todos los INNER JOIN que escribas tendrán la misma performance. Algunos serán más efectivos que otros. La mejor manera que tienes de averiguar cuan efectiva es tu consulta es usando un programa manejador gráfico, tal como el EMS SQL Manager. En este caso si haces click sobre la pestaña «Performance Analysis» podrás ver gráficamente la eficiencia de tu consulta.

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

Veamos un ejemplo:

Tenemos la tabla SUCURSALES con los siguientes datos:

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

y tenemos la tabla BANCOS con los siguientes datos:

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

Como ves, todos los Bancos registrados hasta ahora (16 Bancos en total) pertenecen a la Sucursal número 0, es decir a Asunción. Ahora escribimos una consulta para que nos muestre los nombres de las sucursales y los datos de los Bancos. La tabla SUCURSALES tiene un índice según la columna SUC_CODIGO y la tabla BANCOS tiene un índice según las columnas BAN_CODSUC y BAN_IDENTI.

SELECT
B.BAN_CODSUC,
S.SUC_NOMBRE AS BAN_NOMSUC,
B.BAN_IDENTI,
B.BAN_NOMBRE
FROM
BANCOS B
JOIN
SUCURSALES S
ON B.BAN_CODSUC = S.SUC_CODIGO

y luego, haciendo click sobre «Performance Analysis» averiguamos que tan eficiente es nuestra consulta y esto es lo que obtenemos:

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

Evidentemente una performance muy mala. El SELECT está correcto, el JOIN se realizó correctamente, sin embargo la performance obtenida es pésima. ¿Por qué? porque en la tabla de BANCOS no se está usando un índice y la tabla de SUCURSALES fue recorrida una vez por cada fila de la tabla de BANCOS. Eso es pésimo.

Podemos mejorar esa consulta escribiendo:

SELECT
B.BAN_CODSUC,
S.SUC_NOMBRE AS BAN_NOMSUC,
B.BAN_IDENTI,
B.BAN_NOMBRE
FROM
BANCOS B
JOIN
SUCURSALES S
ON B.BAN_CODSUC = S.SUC_CODIGO
WHERE
B.BAN_CODSUC >= 0

o sea, se agregó una cláusula WHERE obligándole así al Firebird a usar un índice en la tabla de BANCOS. La nueva performance será ahora:

Captura 5. Si haces clic sobre la imagen la verás más grande

Ya está mejor, ahora las dos tablas están usando índices. Sin embargo ¿por qué la tabla de SUCURSALES fue recorrida 16 veces siendo que solamente tenemos 4 sucursales y de esas solamente 1 sucursal está siendo utilizada?

Podemos mejorar esa consulta escribiendo:

SELECT
B.BAN_CODSUC,
S.SUC_NOMBRE AS BAN_NOMSUC,
B.BAN_IDENTI,
B.BAN_NOMBRE
FROM
BANCOS B
JOIN
SUCURSALES S
ON B.BAN_CODSUC = S.SUC_CODIGO
WHERE
B.BAN_CODSUC >= 0 AND
B.BAN_IDENTI > 0

Ahora, este es el gráfico de la performance que obtenemos:

Captura 6. Si haces clic sobre la imagen la verás más grande

Sin dudas que está mejor, ambas tablas están usando índices y la tabla de SUCURSALES solamente fue recorrida 4 veces, o sea 1 vez por cada fila de esa tabla. ¿Pero por qué fue recorrida 4 veces siendo que en la tabla de BANCOS solamente la sucursal con el código 0 es utilizada?

Esta consulta puede ser optimizada para que solamente una fila de la tabla de SUCURSALES sea visitada. ¿Cómo lo harías?

Finalmente, si solamente te interesa la sucursal número 0 lo que puedes escribir es:

SELECT
B.BAN_CODSUC,
S.SUC_NOMBRE AS BAN_NOMSUC,
B.BAN_IDENTI,
B.BAN_NOMBRE
FROM
BANCOS B
JOIN
SUCURSALES S
ON B.BAN_CODSUC = S.SUC_CODIGO
WHERE
B.BAN_CODSUC >= 0 AND
B.BAN_IDENTI > 0 AND
S.SUC_CODIGO = 0

y la performance que obtendrás en ese caso será:

Captura 7. Si haces clic sobre la imagen la verás más grande

Y este es, finalmente, el mejor resultado posible. Lo importante que debes notar aquí es que con la cláusula WHERE se le obligó al Firebird a utilizar índices y que a medida que más columnas se le fueron agregando a la cláusula WHERE más se fue mejorando la performance obtenida.

Las tablas utilizadas en este ejemplo tenían pocas filas, en tablas que tienen miles o millones de filas la ganancia de tiempo puede ser impresionante. Siempre que escribas una consulta o una vista debes verificarla con la pestaña «Performance Analysis» para asegurarte que está optimizada. Y si no lo está, debes optimizarla.

Artículos relacionados:

El índice del blog Firebird21

El foro del blog Firebird21