Las tablas derivadas son una de las construcciones que podemos usar con Firebird pero que poca gente conoce o utiliza. Bien utilizadas pueden resultar muy útiles pues nos permitirán obtener los resultados con mucha mayor velocidad.

Una tabla derivada, como ya habíamos visto en artículos anteriores es un SELECT que se escribe a continuación de la cláusula FROM.

¿Cuál es la ventaja de usarlas?

Que los datos de nuestro SELECT principal ya se encuentran filtrados por el contenido de la tabla derivada. De esta manera, la cantidad de filas a procesar es mucho menor, y por ese motivo el consumo de memoria es pequeño, y la velocidad de respuesta es muy alta.

Veamos un ejemplo para explicar mejor el concepto.

Problema: devolver la primera fila de cada grupo

Tenemos una tabla llamada MOVIMCAB (cabecera de movimientos) donde guardamos los datos de cabecera de cada venta realizada. Y una tabla llamada MOVIMDET (detalles de movimientos) donde guardamos los datos de cada producto vendido, esta tabla es hija de MOVIMCAB y por lo tanto tiene una restricción Foreign Key con ella.

TABLAS_DERIVADAS_01

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

MVC_CODSUC = Código de la sucursal
MVC_IDENTI = Identificador de la fila
MVC_FECHAX = Fecha de la venta
MVC_IDECLI = Identificador del Cliente
MVC_TIPDOC = Tipo de documento
MVC_NRODOC = Número de documento

TABLAS_DERIVADAS_02

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

MOV_CODSUC = Código de la sucursal
MOV_IDENTI = Identificador de la fila
MOV_IDECAB = Identificador de la fila cabecera, o sea la fila de MOVIMCAB
MOV_IDEPRD = Identificador del producto o artículo vendido
MOV_CANTID = Cantidad vendida
MOV_PRECIO = Precio unitario de venta

Lo que deseamos obtener es el primer producto vendido en cada venta. O sea, lo que vemos en la Captura 3.

TABLAS_DERIVADAS_03

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

Solución 1. Sin usar tablas derivadas

SELECT
   T1.MOV_IDENTI,
   T1.MOV_IDECAB,
   T2.MVC_FECHAX,
   T2.MVC_NRODOC,
   T1.MOV_IDEPRD,
   T1.MOV_CANTID,
   T1.MOV_PRECIO
FROM
   MOVIMDET T1
JOIN
   MOVIMCAB T2
      ON MOV_IDECAB = MVC_IDENTI
WHERE
   T1.MOV_IDENTI = (SELECT
                       FIRST 1
                       T2.MOV_IDENTI
                    FROM
                       MOVIMDET T2
                    WHERE
                       T1.MOV_IDECAB = T2.MOV_IDECAB
                    )

TABLAS_DERIVADAS_04

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

Solución 2. Sin usar tablas derivadas

SELECT
   MOV_IDENTI,
   MOV_IDECAB,
   MVC_FECHAX,
   MVC_NRODOC,
   MOV_IDEPRD,
   MOV_CANTID,
   MOV_PRECIO
FROM
   MOVIMDET T1
JOIN
   MOVIMCAB T2
      ON MOV_IDECAB = MVC_IDENTI
WHERE
   NOT EXISTS(SELECT
                 *
              FROM
                 MOVIMDET T3
              WHERE
                 T1.MOV_IDECAB = T3.MOV_IDECAB AND
                 T1.MOV_IDENTI > T3.MOV_IDENTI)

TABLAS_DERIVADAS_05

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

Solución 3. Usando una tabla derivada

SELECT
   T1.MOV_IDENTI,
   T1.MOV_IDECAB,
   T1.MOV_FECHAX,
   T1.MOV_NRODOC,
   T2.MOV_IDEPRD,
   T2.MOV_CANTID,
   T2.MOV_PRECIO
FROM
   (SELECT
       MIN(MOV_IDENTI) AS MOV_IDENTI,
       MOV_IDECAB,
       MVC_FECHAX AS MOV_FECHAX,
       MVC_NRODOC AS MOV_NRODOC
    FROM
       MOVIMDET
    JOIN
       MOVIMCAB
          ON MOV_IDECAB = MVC_IDENTI
    GROUP BY
       2, 3, 4
    ) T1
JOIN
   MOVIMDET T2
      ON T1.MOV_IDENTI = T2.MOV_IDENTI

TABLAS_DERIVADAS_06

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

Análisis

Con las tres consultas se obtiene el resultado deseado pero como podemos observar en los gráficos de sus rendimientos no es lo mismo usar una que otra.

La primera consulta es claramente ineficiente porque lee 2 veces la tabla MOVIMDET, una vez usando índice y la otra vez sin usarlo, así que en tablas grandes, que tienen millones y millones de filas el tiempo que se demorará será muy grande. Podría usarse en tablas pequeñas porque no se notará mucho que es lenta, pero en tablas grandes será inaceptable.

La segunda consulta también lee todas las filas de la tabla MOVIMDET y sin usar un índice. Eso no es un error ni está mal, ya que para obtener el resultado deseado sí o sí hay que leer todas las filas de esa tabla. En este caso, como hay que recorrer toda la tabla, el que no se haya usado un índice es algo positivo, con un índice se demoraría más.

La tercera consulta, usando una tabla derivada, también es muy eficiente. Que no se haya usado un índice en la tabla MOVIMCAB no es algo malo porque como está relacionada con MOVIMDET a través de una Foreign Key cuando se lee una fila de MOVIMDET ya se conoce cual es la fila que le corresponde en MOVIMCAB, así que no es necesario un índice ahí, más bien al contrario: si se usa un índice en MOVIMCAB se demoraría más. ¿Por qué en MOVIMDET se leyeron 13 filas? Porque se leyeron todas las filas de esa tabla (que son 8) y además se leyeron las que cumplían la condición de ser la primera de cada grupo (que son 5). La suma da 13. En MOVIMCAB se leyeron todas las filas de esa tabla, porque sí o sí se necesita leer esa tabla completa.

Conclusión:

Si usamos Firebird normalmente hay varias consultas que podemos escribir para obtener el resultado deseado. Pero eso no significa que el rendimiento es igual en todas esas consultas, no, no es así. En general siempre habrá una consulta que sea mejor que las demás y si somos buenos profesionales deberíamos preocuparnos por encontrar a la mejor consulta posible.

Una de las técnicas que disponemos es a través del uso de tablas derivadas. Con ellas lo que hacemos es filtrar los datos previamente a extraer las filas que nos interesan. En muchos casos (aunque no en todos, por supuesto) el uso de tablas derivadas puede ayudarnos a aumentar la velocidad con la cual se obtienen los resultados deseados.

Entonces, si descubrimos que una consulta está lenta o que lee demasiadas filas de las tablas, tendríamos que pensar en el uso de una tabla derivada, podría ser la mejor solución.

Artículos relacionados:

Tablas derivadas

Entendiendo subconsultas y tablas derivadas

Optimizando los JOIN

Optimizando las subconsultas

El índice del blog Firebird21

El foro del blog Firebird21