back to /blog

SQL tips para correr queries rápido

[ sql  blog  ]

Una nueva manager en mi área revisó mi código y el de mi equipo, y después de ver que las queries duraban minutos, decidió hacer una reunión para educar a todos en SQL, usando nuestro trabajo como ejemplo de lo que no hay que hacer 🥲. Para no pasar esta vergüenza nunca más, me anoté 5 errores para evitar en mis queries.


Lo que no hay que hacer:

  1. SELECT * FROM table
  2. Cartesian Joins
  3. Joins en tablas grandes
  4. DISTINCT
  5. WITH big tables



Lo que está mal con SELECT * FROM table

Hoy en día, los datos se guardan en big data warehouses que se organizan en columnas, como Redshift, Hadoop y Big-Query. En este tipo de almacenamiento, es más eficiente acceder a todas las filas desde una columna que acceder a todas las columnas de una sola fila. Por esto, hay que evitar usar el * al seleccionar todo, porque se está llamando a todas las columnas –> usando el máximo de información de una tabla. Para que se corra más rápido, conviene nombrar solamente los campos que te interesen.

⛔️ SELECT * FROM table ;

SELECT col1, col2 FROM table ;

Notas sobre almacenamiento en columnas - columnar store - y filas - row store

En el row-store la información se guarda y recupera una fila a la vez, son fáciles de leer y escribir, pero no es fácil agregar o recuperar todas las filas al mismo tiempo. Este es el caso más tradicional, que se suele ver en tutoriales, de una “base de datos de estudiantes” en la que es de interés ver los datos de un individuo en particular. Anteriormente se usaba este tipo de almacenamiento, pero fue quedando desactualizado desde que la información se guarda masivamente, ocupando millones de filas.

Ahora se se usa más el columnar-store, re común para información OLAP (online analytical processing). En este caso interesa realizar cálculos sobre campos, por ejemplo, obtener un número total de transacciones y calcular su monto promedio, en vez de revisar un caso en particular.

En ambas situaciones, las tabla se ven exactamente igual, pero el rendimiento es diferente según los tipos de consulta que ejecutamos. Para el almacenamiento orientado a filas, podríamos ver fácilmente toda la información de una unidad determinada, y en el almacenamiento orientado a columnas, podemos procesar agregados en columnas muy rápido. Como en aplicaciones de big data nos interesa realizar cálculos en columnas, hoy en día las bases de datos más comunes siguen este estilo. Más sobre row y columnar stores.


Errores en Joins Cartesianos

Los joins cartersians, a.k.a cross joins, devuelven todas las combinaciones posibles de los registros de dos tablas, por lo que si cruzás dos tablas con N filas, los resultados van a tener NxN filas. A menos que estés diseñando un experimento o creando una vista, en la vida real nunca vas a user esta combinación.

En algunos editores de SQL, este es el join predeterminado cuando no especificás el nombre, por lo que siempre, siempre, siempre, hay que escribir: INNER JOIN o LEFT JOIN. No es necesario ningún otro, ni siquiera RIGHT JOIN porque eso es para gente rara.

⛔️

SELECT
 t1.col1,
 t2.col2
FROM table1 as t1, table2 as t2 ;

SELECT
 t1.col1,
 t2.col2
 FROM table1 as t1
 LEFT JOIN table2 as t2 ON t1.id = t2.t1_id ;


Evitar joins con tablas enormes

Sobre joins: no hay que juntar tablas enormes entre sí. Es mejor hacer un proceso de dos pasos: primero reducir cada tabla original a través de una subquery, y luego unir estas dos subqueries. Aunque parece más complejo tener consultas anidadas, ahorra tiempo, ya que se reduce significativamente el tiempo de ejecución.

⛔️

SELECT
  c.id,
  c.age,
  t.amt

FROM customers c                          
LEFT JOIN transactions t on c.id = t.cust_id

SELECT *    

FROM (
      SELECT id, age
      FROM customers
      ) c        
LEFT JOIN (
      SELECT cust_id, amt
      FROM transactions) t on c.id = t.cust_id

DISTINCT

La sentencia DISTINCT es esencialmente un grupo by, pero es más costosa porque uno se olvida cuánto está agrupando. Se usa para obtener registros únicos, generalmente cuando hay duplicados en tu tabla original o en cualquiera de las tablas que juntaste a través de un join. DISTINCT funciona ordenando primero todos los datos y luego agrupando por todos los campos incluidos en la instrucción SELECT. Es decir, es un group by de todo, por eso es lento.

Usar DISTINCT en varias columnas que no son índices, es costoso en tiempo y memoria, así que conviene evitarlo. En su lugar, se pueden usar tablas temporales con pre-agregados, y evitar uniones con tablas en crudo.


WITH

WITH se usa para nombrar una subquery, para que puedas usarla en tu consulta principal. Esto dicen que es útil para estructurar tu código cuando estás anidando consultas; pero se ejecutan todas a la misma vez. Consultar muchas tablas grandes y llenas al mismo tiempo es una mala idea. A veces, cuando tengo que crear vistas o tablas que involucran muchas combinaciones, como más de 10, es mejor crear tablas temporales intermedias, e ir generándolas de una a la vez.

Por ejemplo, imaginemos que tenemos una tabla de clientes, una tabla de transacciones y una tabla de ciudad. Queremos tener el importe medio de la compra máxima que realiza un cliente en cada ciudad.

En lugar de ejecutar dos consultas al mismo tiempo:

⛔️

WITH my_big_table
  as (SELECT
      c.id,
      c.age,
      a.city,  
      max(t.amt) as max_amt      
      FROM customers c                          
      LEFT JOIN transactions t on c.id = t.cust_id
      LEFT JOIN cust_address a on c.id = a.cust_id
      group by 1,2,3
      )  

  SELECT a.city, avg(t.amt)
  group by 1 ;

Es mejor crear una tabla temporal y hacerlo en dos pasos:

CREATE TEMPORARY TABLE AS max_amt_cust
  ( SELECT
      c.id,
      max(t.amt) as max_amt      
      FROM customers c                          
      LEFT JOIN transactions t on c.id = t.cust_id
      GROUP BY 1
  );

SELECT
  a.city(),
  max(m.amt) as max_amt      
FROM cust_address a
LEFT JOIN max_amt_cust m on m.id = a.cust_id
GROUP BY 1

De este modo quedan las queries mas cortas y mas eficientes, aunque lleve mas trabajo al principio de redactar más subqueries.

Como regla general, si una consulta tarda más de 30 segundos en ejecutarse, es probable que haya cometido alguno de estos cinco errores y que todavía queden oportunidades de mejorar el sql, limitando el número de columnas, corrigiendo joins, haciendo tablas intermedias y group by a mano; evitando los distinct, with y select *.


Comments

Be the first and only to leave a comment ever - via github on this ticket. Comments should appear on this page instantly, but I woudln't be surprised if it's not working (my bad).