En uno de nuestros proyectos necesitamos poder estudiar el comportamiento de los usuarios. Así, tenemos almacenados en base de datos ciertos logs de actividad y por cada acción que realizan que queremos monitorizar guardamos una línea de log.
Para dar un poco de contexto, tenemos dos tablas en una base de datos MySQL: los logs se guardan en una tabla (datalog), en la que la mayor parte de líneas indican únicamente que se ha realizado una acción, pero en otras se indica un valor escalar: tiempo, cantidad asociada. Tenemos también una tabla (items) con la información complementaria sobre el elemento que se está monitorizando: nombre, identificador, categoría…
Si tuviésemos informes tasados por tiempo fijo (informes semanales o mensuales), se trabajaría con un sistema de agregados, pero aquí necesitamos hacer un estudio de las acciones bajo distintos parámetros de estudio, algunos de ellos temporales.
Un acercamiento habitual, y es el que desarrollamos en una primera iteración con solo 3 elementos a contar, era hacer una query por cada elemento y unirlas en una gran query. De principio podía ser una solución «correcta», pero con sucesivas evoluciones se aprecia que no es una solución óptima. A medida que se añaden nuevas columnas para el estudio, se necesita añadir una nueva subquery.
SELECT i.item_id as id, i.item_name as name,
SUM(value_a_aux) as sum_a, SUM(value_b_aux) as count_b, SUM(value_c_aux) as count_c
FROM (
SELECT i1.item_id as item_id, i1.item_name as item_name,
SUM(d1.value) as value_a_aux, COUNT(0) as value_b_aux, COUNT(0) as value_c_aux
FROM items i1
LEFT JOIN datalog d1 ON d1.id = i1.info_id
WHERE
d1.msg_code = "TYPE_A"
AND d1.datetime >= '2000/1/1 00:00:00'
AND d1.datetime < '2020/1/1 00:00:00'
GROUP BY i1.item_id
UNION
SELECT i2.item_id as item_id, i2.item_name as item_name,
SUM(0) as value_a_aux, COUNT(i2.id) as value_b_aux, COUNT(0) as value_c_aux
FROM items g2
WHERE
d2.msg_code = "TYPE_B"
AND d2.datetime >= '2000/1/1 00:00:00'
AND d2.datetime < '2020/1/1 00:00:00'
LEFT JOIN datalog d2 ON d2.id = i2.info_id
GROUP BY i2.item_id
UNION
SELECT i3.item_id as item_id, i3.item_name as item_name,
SUM(0) as value_a_aux, COUNT(0) as value_b_aux, COUNT(i3.id) as value_c_aux
FROM items g2
WHERE d3.msg_code = "TYPE_C"
AND d3.datetime >= '2000/1/1 00:00:00'
AND d3.datetime < '2020/1/1 00:00:00'
LEFT JOIN datalog d3 ON d3.id = i3.info_id
GROUP BY i3.item_id
) AS t1
GROUP BY item_id
Pero esta solución es inmantenible a nivel de código (hay que introducir los modificadores de filtrado en todas las subqueries, que obviamente se puede automatizar programáticamente), pero también tiene severos problemas de recursos: por un lado se realizan N veces las «mismas» búsquedas, y con ello, además, se multiplica el número de líneas resultantes que se le pasan a la query superior por el número de subqueries.
Y todo esta query, que además de asustar en tamaño es muy dura para la base de datos, se puede reescribir.
de esta otra manera:
SELECT i.item_id as id, i.item_name as name,
SUM(CASE WHEN b.msg_code = "TYPE_A" THEN value1 ELSE 0 END) as sum_a,
SUM(CASE WHEN b.msg_code = "TYPE_B" THEN 1 ELSE 0 END) as count_b,
SUM(CASE WHEN b.msg_code = "TYPE_C" THEN 1 ELSE 0 END) as count_c
FROM item i
LEFT JOIN datalog d ON b.id = i.info_id
WHERE d.datetime >= '2000/1/1 00:00:00'
AND d.datetime < '2020/1/1 00:00:00'
La solución elegante, no solo es más manejable y óptima, además nos permite hacer sumas o trabajar con los datos resultantes de una manera más sencilla, sería tan facil como cambiar el 1 por el campo a sumar.
Benchmark de la optimización
«When you cannot measure it, when you cannot express it in numbers, your knowledge is of a meagre and unsatisfactory kind» (Lord Kelvin)
Sabíamos que la primera solución se iba degradando en tiempos según crecía la base de datos y las columnas, pero ¿cuanto? Para ello es necesario saber en cuanto tiempo se ejecuta con los mismos datos y controlando tiempos.
Aunque existen potentes herramientas para realizar benchmarking y muchos factores que pueden afectar al rendimiento de las consultas como la máquina donde se ejecuta, la optimización de la base de datos, la concurrencia, etc. en este caso no es necesario hilar tan fino. Aquí queremos comparar dos queries, así que los factores de control son usar el mismo set de datos en la misma máquina. Además, esta base de datos se usa solo para reportes, está aislada de la que se utiliza en la aplicación, con lo que no habrá problemas de concurrencia una vez la pongamos en producción. Estas pruebas se han hecho en un entorno local con MySQL Workbench que por cada consulta te devuelve el tiempo de ejecución.
Pero cuidado, las herramientas de bases de datos tienen distintos mecanismos para optimizar las consultas. El principal con el que nos enfrentamos aquí y que nos podría falsear los resultados es el cacheo de queries. Por eso, para hacer pruebas de rendimiento en MySQL añadimos el modificador: SQL_NO_CACHE justo después de SELECT.
El resultado es que contra el mismo set de datos, la primera query nos está dando tiempos de alrededor de 1,35 segundos (+/- 0,35), por los 0,47 segundos (+/- 0,08) de la segunda. Ambos casos son tiempos bastante altos, pero podemos ver que proporcionalmente hemos ahorrado mucho tiempo. Con solo una columna los tiempos son similares en ambos casos: alrededor de 0,4 segundos (+/- 0,1).
Por añadir, comentar que el umbral de espera de un usuario en una web generalista es de 3 segundos, así que hay que alejarse de estos tiempos o pensará que la aplicación está rota. De todas maneras, en una herramienta de reporte como esta, un usuario especialista puede acostumbrarse o entender tiempos superiores. En este caso hemos ahorrado tiempos en la capa de base de datos, pero aún hay muchas otras capas de aplicación que hacen que la solución final tarde casi 1 segundo en producción para un set de datos similar.
[av_team_member name=’Pablo Rodríguez Monedero’ job=’Técnico de Tuercas y Tornillos de Torsión Temporal’ src=» attachment=» attachment_size=» description=’@yondemon’ font_color=» custom_title=’bla’ custom_content=’ble’ av_uid=’av-261j3u’]
