¿Por qué deben escribirse primero los INNER JOIN?

Deja un comentario

En el artículo:

Poniendo los JOIN en el orden correcto

ya había mostrado que los INNER JOIN siempre deben escribirse antes que los OUTER JOIN pero no había explicado claramente el motivo del por qué deba ser así.

Me explayaré más sobre ese tema ahora.

Si tenemos una consulta como la siguiente:

Listado 1

SELECT
   MiColumna1,
   MiColumna2,
   MiColumna3
FROM
   MiTabla1
JOIN
   MiTabla2
      ON MiCondición1
JOIN
   MiTabla3
      ON MiCondición2

El optimizador del Firebird puede cambiar el orden de las tablas pues como se trata de un INNER JOIN todas las filas deben estar emparejadas y por lo tanto no importa cual se coloque primero. O sea que si después de la cláusula FROM ponemos a MiTabla1 o la ponemos a MiTabla2 o la ponemos a MiTabla3 el resultado no variará, será idéntico. Así mismo, en el primer o en el segundo JOIN podemos poner a MiTabla1, a MiTabla2, o a MiTabla3, y siempre obtendremos lo mismo.

Sin embargo, aunque el resultado final será el mismo, la eficiencia no será la misma. El optimizador del Firebird realiza su tarea mirando (entre otras cosas) la selectividad de los índices y el tiempo que demorarán. Puede quizás descubrir que para obtener la máxima eficiencia después de la cláusula FROM debe colocar a MiTabla2 y que el primer JOIN debe ser con MiTabla3.

Entonces, si lo considera necesario el optimizador puede tranquilamente poner a otra tabla después del FROM o cambiar el orden de los JOIN, no hay problema con eso porque el resultado obtenido será exactamente el mismo.

En síntesis, el optimizador intentará optimizar la consulta.

¿Y qué ocurre cuándo escribimos un LEFT JOIN?

Listado 2

SELECT
   MiColumna1,
   MiColumna2,
   MiColumna3
FROM
   MiTabla1
LEFT JOIN
   MiTabla2
      ON MiCondición1

Listado 3

SELECT
   MiColumna1,
   MiColumna2,
   MiColumna3
FROM
   MiTabla2
LEFT JOIN
   MiTabla1
      ON MiCondición1

Lo que ocurre es que los resultados devueltos por Listado 2 y por Listado 3 ya no serán idénticos si alguna de las columnas escritas después de la clásula SELECT puede ser NULL o si (este es el caso más común) la cantidad de filas de MiTabla1 no es igual a la cantidad de filas de MiTabla2.

Ejemplo: Si MiTabla1 tiene 5 filas y MiTabla2 tiene 20 filas, el resultado del SELECT puede tener 5 filas (si después de la cláusula FROM escribimos MiTabla1) o puede tener 20 filas (si después de la cláusula FROM escribimos MiTabla2). Evidentemente, son muy diferentes esos resultados.

En el Listado 1 no importaba cual tabla se escribía en el JOIN porque como todas las filas debían estar emparejadas daba lo mismo poner a una tabla o a la otra. Sin embargo, en el Listado 2 y en el Listado 3 eso ya no es verdad, aquí sí importa cual tabla se escribe en el LEFT JOIN y los resultados obtenidos pueden ser muy distintos en cada caso.

Conclusión:

Siempre que ejecutamos un SELECT el optimizador del Firebird intenta que ese SELECT muestre los resultados lo más rápidamente posible, para ello mira entre otras cosas la selectividad de los índices. Cuando usamos INNER JOIN puede cambiar el orden de las tablas porque eso no importa, ya que el resultado final será el mismo y si descubre que cambiando el orden de las tablas los resultados se mostrarán más rápido, entonces cambia el orden de las tablas. Pero cuando usamos LEFT JOIN ya no puede cambiar el orden de las tablas porque en la gran mayoría de los casos los resultados serán distintos. Entonces, deja de optimizar, allí mismo termina su tarea. La primera vez que encuentra un LEFT JOIN, RIGHT JOIN, o FULL JOIN, finaliza la optimización. Y es lógico, porque ya nada puede mejorarse.

Entonces, si nuestro SELECT conteniendo un OUTER JOIN (o sea, LEFT JOIN, RIGHT JOIN, o FULL JOIN) estaba optimizado, todo bien. Si no estaba optimizado, todo mal, ya que el optimizador del Firebird no lo mejorará.

¿Corolario?

Siempre, pero siempre, en todos los casos, debemos escribir primero los INNER JOIN y solamente después de ellos escribir los OUTER JOIN. De esa manera nos aseguraremos de que el optimizador del Firebird haga su trabajo y mejore si es posible la velocidad con la cual se obtienen los resultados.

Artículos relacionados:

Poniendo los JOIN en el orden correcto

El índice del blog Firebird21

El foro del blog Firebird21

Anuncios

Optimizando los JOIN

9 comentarios

En muchas de nuestras consultas tenemos que relacionar dos o más tablas usando la cláusula JOIN. En general Firebird recupera muy rápido los datos, pero podemos conseguir aún una mayor velocidad con la técnica mostrada a continuación.

Un JOIN normal sería algo como:

SELECT
   *
FROM
   MiTabla1
JOIN
   MiTabla2
      ON MiRelación_entre_MiTabla1_y_MiTabla2
WHERE
   MiCondición

Funciona muy bien y obtendremos lo que queremos pero podemos aumentar la velocidad de respuesta si a esa misma consulta la escribimos así:

SELECT
   *
FROM
   (SELECT
       MiColumna1,
       MiColumna2,
       MiColumna3
    FROM
       MiTabla1
    WHERE
       MiCondición
   ) AS MiFiltro
JOIN
   MiTabla2
      ON MiRelación_entre_MiFiltro_y_MiTabla2

¿Qué hicimos aquí?

  • Primero, filtramos los datos de la tabla principal para obtener solamente las filas que nos interesan. O sea que construimos lo que se llama una tabla derivada
  • Segundo, relacionamos el filtro de la tabla principal (o sea, la tabla derivada) con la tabla secundaria

 En un JOIN normal primero se relacionan las dos tablas y luego se filtra el conjunto resultado. En el JOIN optimizado en cambio primero se filtra la tabla principal y luego se relacionan las dos tablas. Eso en general (pueden existir excepciones) nos permitirá obtener más rápidamente el conjunto resultado.

Conclusión:

Muchas veces podemos obtener más rápidamente el conjunto resultado si usamos una tabla derivada para filtrar las filas de la tabla principal antes de relacionarla con la tabla secundaria. Si necesitas mayor velocidad en un SELECT que tiene JOIN y que tiene WHERE, prueba esta técnica y verifica el tiempo de respuesta.

Artículos relacionados:

Tablas derivadas

Entendiendo subconsultas y tablas derivadas

El índice del blog Firebird21

El foro del blog Firebird21

Optimizando las subconsultas

2 comentarios

Las subconsultas son una herramienta muy útil que tenemos a nuestra disposición pero que en ocasiones pueden ser muy lentas. Consideremos este caso:

SELECT
   C.MVC_FECHAX,
   C.MVC_NRODOC,
   (SELECT SUM (D.MOV_CANTID * D.MOV_PRECIO) FROM MOVIMDET D WHERE D.MOV_IDECAB = C.MVC_IDENTI),
   (SELECT MAX (D.MOV_CANTID * D.MOV_PRECIO) FROM MOVIMDET D WHERE D.MOV_IDECAB = C.MVC_IDENTI),
   (SELECT MIN (D.MOV_CANTID * D.MOV_PRECIO) FROM MOVIMDET D WHERE D.MOV_IDECAB = C.MVC_IDENTI),
   (SELECT AVG (D.MOV_CANTID * D.MOV_PRECIO) FROM MOVIMDET D WHERE D.MOV_IDECAB = C.MVC_IDENTI)
FROM
   MOVIMCAB C

Aquí, tenemos una tabla cabecera de movimientos (MOVIMCAB) y una tabla de detalles de movimientos (MOVIMDET).

Y lo que nos interesa es hallar, para cada venta realizada, el total de esa venta (usamos la función SUM()), la línea con el mayor total (usamos la función MAX()), la línea con el menor total (usamos la función MIN()), y el promedio de las líneas (usamos la función AVG()).

Todo bien hasta aquí y el SELECT anterior cumple con su objetivo pero … hay un pero. Y ese pero es que escribimos 4 subconsultas. Y cada subconsulta es independiente a las otras y por lo tanto se emplea mucho tiempo en obtener los resultados deseados. Se los obtiene, sí, pero se demora mucho en obtenerlos (por supuesto que en el caso de tablas muy grandes, si las tablas son pequeñas no se notará la demora).

Entonces, ¿podemos optimizar este SELECT para que se ejecute más rápido?

Sí, podemos.

El truco es realizar una sola subconsulta, y en ella obtener todos los datos que nos interesan, como vemos a continuación:

 

SELECT
   C.MVC_FECHAX,
   C.MVC_NRODOC,
   CAST(LEFT     (TodoJunto, 10)            AS BIGINT),
   CAST(SUBSTRING(TodoJunto FROM 11 FOR 10) AS BIGINT),
   CAST(SUBSTRING(TodoJunto FROM 21 FOR 10) AS BIGINT),
   CAST(RIGHT    (TodoJunto, 10)            AS BIGINT)
FROM
   (SELECT
       MOVIMCAB.MVC_IDENTI,
       (SELECT
           LPAD(SUM(MOV_CANTID * MOV_PRECIO), 10) ||
           LPAD(MAX(MOV_CANTID * MOV_PRECIO), 10) ||
           LPAD(MIN(MOV_CANTID * MOV_PRECIO), 10) ||
           LPAD(AVG(MOV_CANTID * MOV_PRECIO), 10)
        FROM
           MOVIMDET
        WHERE
           MOV_IDECAB = MVC_IDENTI) AS TodoJunto
    FROM
       MOVIMCAB) AS DERIVADA
JOIN
   MOVIMCAB C
      ON C.MVC_IDENTI = DERIVADA.MVC_IDENTI

 Aquí, para cada fila de la tabla MOVIMDET se realiza una sola subconsulta y se concatenan las columnas para guardarlas con el alias TodoJunto.

Como puedes ver, esta consulta es más complicada de escribir que la original, pero la ventaja es que se obtienen los resultados mucho más rápidamente porque cada fila de la tabla MOVIMDET es recorrida una sola vez, no 4 veces como en la consulta original.

Conclusión:

Se pueden optimizar las subconsultas concatenando las columnas que nos interesan y luego “desconcatenarlas”.

Esta técnica para optimizar las subconsultas es muy útil cuando las tablas son muy grandes (es decir: tienen millones de filas) porque se consigue obtener los resultados muy rápido. El motivo es que cada fila de la tabla de detalles se recorre una sola vez.

Si no usamos esta técnica, cada fila de la tabla de detalles sería recorrida 4 veces. Y recorrer las filas 4 veces evidentemente es mucho más lento que recorrerlas 1 sola vez.

Sin embargo, si las tablas son pequeñas y siempre lo serán, probablemente no valga la pena el esfuerzo de optimizar las subconsultas.

Así que queda a tu criterio si decides optimizarlas o no, pero al menos ya conoces una técnica para optimizarlas si lo deseas.

Artículos relacionados:

Tablas derivadas

Entendiendo subconsultas y tablas derivadas

El índice del blog Firebird21

 

Verificando la ejecución óptima de los stored procedures

1 comentario

Así como podemos verificar que los SELECT estén optimizados, también podemos hacer lo mismo con los stored procedures, aquí tenemos un ejemplo:


EXECUTE PROCEDURE GENERAR_ASIENTOS_2(2013)

llama al stored procedure GENERAR_ASIENTOS_2 el cual recibe un parámetro de entrada que corresponde al año. Al verificar su ejecución con el programa EMS SQL Manager nos encontramos con esto:

VERIFICAR1

(si haces clic en la imagen la verás más grande)

¿Más de 97 millones de registros de venta hasta mayo de 2013? eso es una total exageración, hay algo que está muy mal ahí. ¿Más de 18 millones de compras hasta mayo de 2013? imposible. ¿6 minutos y 25 segundos para completar la ejecución? demasiado tiempo, hay que acortar eso. Así que optimizamos el stored procedure para asegurarnos de que ahora sí use índices y el resultado es el siguiente:

STORED2

(si haces clic en la imagen la verás más grande)

 Se trata del mismo stored procedure pero ahora usando los índices correctos. Y la diferencia es grandísima.

  • En el primer caso la tabla VENTASDET fue leída 97.541.444 veces, en el segundo caso solamente 18.629 veces
  • En el primer caso la tabla COMPRASDET fue leída 18.666.752 veces, en el segundo caso solamente 7.384 veces
  • En el primer caso la tabla ASIENTOSDET fue leída 473.588 veces, en el segundo caso solamente 44.660 veces
  • En el primer caso la ejecución del stored procedure demoró 6 minutos y 25 segundos. En el segundo caso demoró 8 segundos

Como puedes ver, se ha logrado una mejora sustancial al usar los índices correctos y optimizar el código. Es por ese motivo que siempre debes verificar que la ejecución de tu stored procedure sea óptima. A veces, sin darte cuenta cometes algunos pequeños errores cuyas consecuencias pueden ser catastróficas (en este caso, el problema fue que los índices eran CMC_CODSUC, CMC_IDENTI en la tabla COMPRASCAB y VTC_CODSUC, VTC_IDENTI en la tabla VENTASCAB, pero en las consultas había puesto CMC_IDENTI y VTC_IDENTI, por lo tanto no se usaban los índices y ambas tablas se recorrían muchísimas más veces de las necesarias).

También es conveniente hacer clic en la pestaña “Summary” para ver un resumen de todas las inserciones, actualizaciones, borrados y consultas.

VERIFICAR3

(si haces clic en la imagen la verás más grande)

 Conclusión:

Siempre debes verificar que tus stored procedures estén optimizados porque aunque a primera vista puedan parecerte que está todo ok pueden tener algunos errores cuyas consecuencias pueden ser drásticas.

Artículos relacionados:

Usando índices correctos para aumentar la velocidad de las consultas

El índice del blog Firebird21

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