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