Queries Correladas: Implementaciones y rendimiento

0
558

En este tutorial vamos a ver diferentes formas de implementar queries correladas y el impacto que tiene en el rendimiento y su tiempo de ejecución.

Índice de contenidos

1. Introducción

Como continuación del tutorial anterior sobre las formas de medir el rendimiento de las queries, en este tutorial analizaremos diferentes formas de implementar queries correladas o subconsultas y el impacto que tiene sobre el rendimiento y tiempo de ejecución.

2. Entorno

El tutorial está escrito usando el siguiente entorno:

  • Hardware: MacBook Pro Retina 15′ (2,5 Ghz Intel Core i7, 16GB DDR3).
  • Base de datos Postgres 12
  • Python 3.
  • Sistema Operativo: Mac OS Catalina 10.15.4.
  • Docker versión 18.06.1-ce, build e68fc7a

3. Preparando la base datos

Como entorno de pruebas, usaremos la base de datos sobre películas importada en el anterior tutorial donde hablaba de formas de medir el rendimiento de tus queries en Postgres.

4. Queries Correladas: Implementaciones y rendimiento

En este apartado realizaremos diferentes implementaciones sobre el mismo concepto de query. Imaginemos que nos piden que hagamos una query capaz de recuperar las 10 películas con más presupuesto de media de toda la base de datos.

Como primera aproximación se nos puede ocurrir seria usar DISTINCT y ordenar descendentemente por budget. Una posible implementación podría ser la siguiente:

SELECT DISTINCT title, 
                budget 
FROM   movies 
WHERE  budget IS NOT NULL 
ORDER  BY budget DESC, 
          title DESC 
LIMIT  10;

Un tutorial sobre la optimización de subqueries que da una solución que no utiliza subqueries, !!BIEN !!. Así que para resolver el problema nos vamos a poner la restricción de implementarla con una query correlada.

En los siguientes apartados os mostraremos diferentes implementaciones de esta misma query objetivo con queries correladas.

4.1. En el WHERE

Una posible implementación sería utilizar una subquery como criterio en la cláusula WHERE:

SELECT m.title, 
       m.budget 
FROM   movies m 
WHERE  m.budget IS NOT NULL 
       AND m.budget IN (SELECT DISTINCT budget 
                        FROM   movies 
                        WHERE  budget IS NOT NULL 
                               AND m.title = title 
                        ORDER  BY budget DESC) 
ORDER  BY budget DESC, 
          title DESC 
LIMIT  10

Pero si quisiéramos hacer uso de la función AVG para aplicar explícitamente los criterios de la query solicitada («las 10 películas con más presupuesto que la media») quedaría algo así:

SELECT m.title, 
       m.budget 
FROM   movies m 
WHERE  m.budget IS NOT NULL 
       AND m.budget >= (SELECT Avg(budget) AS avg_budget 
                        FROM   movies 
                        WHERE  budget IS NOT NULL 
                               AND m.title = title 
                        GROUP  BY title 
                        ORDER  BY avg_budget DESC, 
                                  title DESC) 
ORDER  BY budget DESC, 
          title DESC 
LIMIT  10;

4.2. Usando INNER JOIN

Esta sería otra posible implementación realizando un INNER JOIN con la subquery:

SELECT m.title, 
       m.budget 
FROM   movies m 
       INNER JOIN (SELECT title, 
                          Avg(budget) AS avg_budget 
                   FROM   movies 
                   WHERE  budget IS NOT NULL 
                   GROUP  BY title) temp 
               ON m.title = temp.title 
WHERE  m.budget IS NOT NULL 
       AND m.budget >= temp.avg_budget 
ORDER  BY budget DESC, 
          title DESC 
LIMIT  10;

4.3. Como otro campo de la query

Una alternativa es incluir los resultados de la subquery como un campo de la select y utilizar este campo como criterio del WHERE. Una posible implementación podría ser la siguiente:

SELECT * 
FROM   (SELECT m.title, 
               m.budget, 
               (SELECT Avg(budget) 
                FROM   movies 
                WHERE  budget IS NOT NULL 
                       AND m.title = title 
                GROUP  BY title) AS avg_budget 
        FROM   movies m 
        WHERE  m.budget IS NOT NULL) AS temp 
WHERE  budget >= avg_budget 
ORDER  BY avg_budget DESC, 
          title DESC 
LIMIT  10;

4.4. Usando VIEW

Otra posibilidad sería crear una vista con la query que vamos a utilizar como criterio, en nuestro caso la select con la media. La vista y la query que la usa quedaría así:

CREATE view avg_budget
AS 
  SELECT title, 
         Avg(budget) AS avg_budget 
  FROM   movies 
  WHERE  budget IS NOT NULL 
  GROUP  BY title 
  ORDER  BY avg_budget DESC, 
            title DESC; 

SELECT m.title, 
       m.budget 
FROM   movies m 
       INNER JOIN avg_budget
               ON m.title = avg_budget.title 
WHERE  m.budget >= avg_budget.avg_budget 
ORDER  BY avg_budget DESC, 
          title DESC 
LIMIT  10;

Esta alternativa como ventaja simplifica la query y facilita su lectura. Como posible desventaja sería la creación de un nuevo objeto en la base de datos para la realización de la consulta, es decir, la vista creada.

4.5. Usando WITH

Una alternativa similar a la anterior sería utilizar la sentencia WITH.

WITH avg_budget_cte AS 
( 
         SELECT   title, 
                  Avg(budget) AS avg_budget 
         FROM     movies 
         WHERE    budget IS NOT NULL 
         GROUP BY title 
         ORDER BY avg_budget DESC, 
                  title DESC) 
SELECT     m.title, 
           m.budget 
FROM       avg_budget_cte cte 
INNER JOIN movies m 
ON         cte.title = m.title 
WHERE      m.budget >= cte.avg_budget limit 10;

WITH nos permite dividir las consultas complicadas en partes más simples. Estas declaraciones denominadas Common Table Expressions o CTE , pueden considerarse como tablas temporales que existen solo para una consulta. También pueden usarse con sentencias SELECT, INSERT, UPDATE, o DELETE.

5. Comparativa de rendimiento

Para realizar esta comparativa utilizaremos las estadísticas de Postgres tal y como indicamos en el tutorial anterior. A las queries anteriormente expuestas añadiremos un campo vacío (») con un alias que identifique de qué tipo de implementación se trata y así nos sea más fácil interpretar los resultados. Los resultados obtenidos son los siguientes:

En esta tabla se muestran los rendimientos de las diferentes implementaciones de

A la vista de los resultados podemos sacar en claro lo siguiente:

  • El top 3 con el mejor rendimiento son las subqueries implementado con INNER JOIN, VISTA y WITH (de menor a mayor tiempo). Con un rendimiento aproximado de las 3 implementaciones de 20 ms.
  • La peor implementación, (la subquery como campo de la select) es de casi 100 ms. Un 80% más que las mejores implementaciones.
  • El uso de la función de agrupación AVG incrementa el tiempo en casi un 25% (de 49 a 63 ms).

¿Y si no hubiéramos tenido la restricción de usar una subquery (por el tema central del tutorial) para realizar la implementación de la query?. Estos son los resultados comparando la primera implementación (sin subquery y con el distinct) y la implementación que ha obtenido el mejor rendimiento.

Comparativa con la query distinct frente a la mejor implementada con la subquery

Como podemos ver hay un incremento de un 50% (de 8 a 18 ms).

6. Conclusiones

Como hemos visto en el tutorial, existe una diferencia importante en el rendimiento dependiendo de las diferentes implementaciones para obtener un mismo resultado. Y si mantenemos la implementación lo más simple posible y nos paramos a pensar un poco, obtendremos los mejores resultados.

Espero que os sirva este tutorial para cuando tengáis la imposición (y esta vez no fijada por mi) de utilizar una subquery y podáis mejorar el rendimiento.

7. Referencias

DEJA UNA RESPUESTA

Por favor ingrese su comentario!

He leído y acepto la política de privacidad

Por favor ingrese su nombre aquí

Información básica acerca de la protección de datos

  • Responsable:
  • Finalidad:
  • Legitimación:
  • Destinatarios:
  • Derechos:
  • Más información: Puedes ampliar información acerca de la protección de datos en el siguiente enlace:política de privacidad