Ejemplo Nº 004 – Usando un INNER JOIN para autoreferenciar una tabla

3 comentarios

Una tabla (o una vista) puede referenciarse a sí misma. Para que esto sea posible debe tener 2 columnas (o más de 2 columnas) que tengan una relación entre sí.

Por ejemplo, tenemos una tabla llamada CUENTAS que mantiene los datos de las cuentas contables con esta estructura:

CUENTAS1

(haciendo click en la imagen la verás más grande)

y cuyas primeras filas son estas:

CUENTAS2

(haciendo click en la imagen la verás más grande)

En este caso en la columna CUE_NIVSUP (en la imagen se la llamó “Cuenta superior”) se coloca el número de la cuenta padre de cada cuenta. La cuenta ACTIVO no tiene cuenta superior porque no depende de ninguna otra cuenta. La cuenta ACTIVO CORRIENTE depende de ACTIVO, entonces esa es su cuenta superior. La cuenta DISPONIBILIDADES depende de ACTIVO CORRIENTE, entonces esa es su cuenta superior, y así sucesivamente.

Como se puede ver, el número registrado en la columna CUE_NIVSUP es un número de cuenta, un número que fue previamente registrado en la columna CUE_NUMERO.

Eso implica que entre ambas columnas (CUE_NUMERO y CUE_NIVSUP) hay una relación: todos los números de cuenta que pueden registrarse en la columna CUE_NIVSUP deben existir en la columna CUE_NUMERO.

Supongamos ahora que deseamos ver una consulta similar a la de arriba pero no solamente con los números de las cuentas de nivel superior sino también con sus respectivos nombres. En este caso, estaríamos relacionando a la tabla de CUENTAS con sí misma, porque ambas columnas pertenecen a la misma tabla.

La tabla de CUENTAS tiene un índice según las columnas: CUE_ANOEJE, CUE_CODSUC, CUE_NUMERO, CUE_NUMSUB, entonces esta consulta nos mostrará el resultado deseado.

SELECT
   C.CUE_IDENTI AS "Identi",
   C.CUE_ANOEJE AS "Año",
   C.CUE_CODSUC AS "Suc.",
   C.CUE_NUMERO AS "Número",
   C.CUE_NUMSUB AS "NumSub",
   C.CUE_NOMBRE AS "Nombre de la cuenta",
   C.CUE_NIVSUP AS "Cuenta superior",
   D.CUE_NOMBRE AS "Nombre cuenta superior",
   C.CUE_NIVELX AS "Nivel",
   C.CUE_ASENTA AS "Asentable"
FROM
   CUENTAS C
JOIN
   CUENTAS D
      ON C.CUE_ANOEJE = D.CUE_ANOEJE AND
         C.CUE_CODSUC = D.CUE_CODSUC AND
         C.CUE_NIVSUP = D.CUE_NUMERO AND
         D.CUE_NUMSUB = ''
WHERE
   C.CUE_ANOEJE = 2008

Lo que obtendremos al ejecutar ese SELECT será esto:
CUENTAS3
(haciendo click en la imagen la verás más grande)

que es casi, casi, lo que deseamos. Las que están faltando son las cuentas de nivel 1, o sea todas las cuentas que no tienen una cuenta superior. Eso es muy fácil de solucionar (en lugar de un INNER JOIN escribiríamos un LEFT JOIN y listo).

Lo importante de este ejemplo es ver como una tabla puede relacionarse a sí misma. Para ello la tabla principal (la que se encuentra a continuación de la cláusula FROM) debe ser la misma que la tabla secundaria (la que se encuentra a continuación de la cláusula JOIN) pero para poder diferenciarlas deben tener alias (en este ejemplo se usó el alias “C” para la tabla principal y el alias “D” para la tabla secundaria).


			

Ejemplo Nº 003 – Escribiendo varios INNER JOIN

1 comentario

La cláusula INNER JOIN (o solamente JOIN, es lo mismo) puede escribirse varias veces en un solo SELECT. El primer JOIN obtiene un conjunto resultado, el segundo JOIN usa ese conjunto resultado más los datos de la segunda tabla o vista para obtener otro conjunto resultado y así sucesivamente.

El primer conjunto resultado siempre es el que más filas tiene, los demás pueden tener igual cantidad de filas o menor cantidad de filas, pero nunca mayor cantidad de filas. Por ejemplo:

El primer JOIN devuelve 40 filas, el segundo JOIN puede devolver 40 filas o menos de 40 filas. Supongamos que devolvió 30 filas. Entonces el tercer JOIN puede devolver 30 filas o menos que 30 filas. Supongamos que devolvió 25 filas. El cuarto JOIN puede devolver 25 filas o menos de 25 filas, y así sucesivamente.

SELECT
   P.PRD_CODSUC,
   S.SUC_NOMBRE AS PRD_NOMSUC,
   P.PRD_CODIGO,
   P.PRD_NOMBRE,
   P.PRD_CODMAR,
   M.MAR_NOMBRE
FROM
   PRODUCTOS P
JOIN
   SUCURSALES S
      ON P.PRD_CODSUC = S.SUC_CODIGO
JOIN
   MARCAS M
      ON P.PRD_CODSUC = M.MAR_CODSUC AND
         P.PRD_CODMAR = M.MAR_CODIGO

Aquí podemos ver que hay dos JOIN. El primero relaciona a la tabla de PRODUCTOS con la tabla de SUCURSALES y el segundo relaciona a la tabla de PRODUCTOS con la tabla de MARCAS. La tabla de MARCAS tiene un índice compuesto por las columnas MAR_CODSUC y MAR_CODIGO, por eso el JOIN se hace sobre ambas columnas.

Supongamos que la tabla de PRODUCTOS tenga 2.000 filas y la tabla de SUCURSALES tenga 4 filas, pero de esas 4 filas solamente 3 se están usando en la tabla de PRODUCTOS. Entonces en el conjunto resultado del primer JOIN solamente podremos referenciar a esas 3 filas.

Supongamos ahora que la tabla de MARCAS tiene 125 marcas pero solamente 112 de ellas se están usando en la tabla de PRODUCTOS. Entonces en el conjunto resultado solamente  podremos referenciarnos a esas 112 marcas.

Ejemplo Nº 002 – Usando INNER JOIN eficientemente

14 comentarios

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.

PerformanceAnalysis

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:

SUCURSALES

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

y tenemos la tabla BANCOS con los siguientes datos:

BANCOS

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:

CONSULTA1
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:

PerformanceAnalysis3
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:

PerformanceAnalysis4

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á:

PerformanceAnalysis5

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