Optimización Avanzada de Consultas SQL para el Análisis Superchain en Tokenflow

Introducción

Recientemente he profundizado en el mundo de Optimism, especialmente en la Superchain, y he explorado las ventajas de herramientas como TokenFlow, que facilitan la visualización de métricas y análisis complejos. Aprovechando mi conocimiento en SQL, decidí crear pequeños widgets y gráficos que simplifican la visualización de los cuatro pilares del análisis de la Superchain mediante la visualización de transacciones, direcciones activas, contratos desplegados y fees pagados.

Durante este proceso, comparé una de mis consultas con otras ya existentes y descubrí diferencias significativas en el rendimiento. Esto me llevó a reflexionar sobre la importancia de estructurar correctamente un widget para TokenFlow desde el inicio, asegurando así una optimización que aproveche mejor los recursos de la plataforma. Esto no solo mejoraría el rendimiento general, sino que también impactaría directamente en el tiempo de visualización. A continuación, describo los casos de uso, los resultados obtenidos y las optimizaciones aplicadas.

---

Explicación y Justificación de las Consultas

Al analizar y construir consultas SQL para las blockchains de la Superchain, implementé varias optimizaciones para mejorar el rendimiento sin comprometer la precisión. Aquí detallo las optimizaciones clave:

1. Selección de columnas específicas:

   • En lugar de utilizar SELECT *, seleccioné únicamente las columnas necesarias para cada pilar, como block_date, gas_used, effective_gas_price, entre otras. Esto reduce la cantidad de datos procesados y disminuye la carga del sistema.

2. Filtrado por fecha optimizado:

   • Apliqué el filtro block_date >= DATEADD('month', -{{n_months}}, CURRENT_DATE()) directamente en cada subconsulta. Así, cada conjunto de datos se filtra antes de realizar uniones o combinaciones, evitando procesamientos innecesarios.

3. Uso de SUM con CASE:

   • Para agregar datos por blockchain, utilicé SUM(CASE WHEN chain = 'Zora' THEN daily_transactions ELSE 0 END). Esto permite realizar agregaciones de manera eficiente dentro de la misma consulta sin recurrir a operaciones como PIVOT, mejorando el rendimiento.

4. Uso de UNION ALL en lugar de JOIN:

   • Al emplear UNION ALL para combinar datos de diferentes blockchains, evitamos duplicar filas y mantenemos la simplicidad de la consulta. Esto es especialmente útil en entornos con grandes volúmenes de datos donde las operaciones JOIN pueden ser costosas.

---

Optimización de Consultas: Casos de Uso

Caso de Uso 1: Transacciones Diarias

Consulta sin optimizar:

La consulta combinaba los datos de todas las blockchains en un solo conjunto antes de contar las transacciones diarias, lo que resultaba menos eficiente en rendimiento.

WITH superchain_transactions AS (
    SELECT *, 'Zora' AS chain FROM zora.core.TRANSACTIONS
    UNION ALL
    SELECT *, 'OP Mainnet' AS chain FROM optimism.core.transactions
    UNION ALL 
    SELECT *, 'Mint' AS chain FROM mint.core.transactions
    UNION ALL
    SELECT *, 'Mode' AS chain FROM mode.core.transactions
)

SELECT * FROM (
    SELECT chain,
           block_date AS date,
           COUNT(*) AS "Daily Transactions"
    FROM superchain_transactions
    WHERE block_date >= DATEADD('month', -{{n_months}}, CURRENT_DATE())
    GROUP BY chain, block_date
    ORDER BY block_date, chain
) 
PIVOT (
    MAX("Daily Transactions") FOR chain IN ('Zora', 'OP Mainnet', 'Mint', 'Mode')
) AS p(date, zora, "OP Mainnet", mint, mode)
ORDER BY date;

Resultado:

• 94 filas / 2.39 KB en 5,879 ms

Consulta optimizada:

En esta versión, cada conjunto de transacciones se agrega por separado y luego se combinan, evitando procesamientos innecesarios.

WITH superchain_transactions AS (
    SELECT block_date, COUNT(*) AS daily_transactions, 'Zora' AS chain
    FROM zora.core.transactions
    WHERE block_date >= DATEADD('month', -{{n_months}}, CURRENT_DATE())
    GROUP BY block_date

    UNION ALL

    SELECT block_date, COUNT(*) AS daily_transactions, 'OP Mainnet' AS chain
    FROM optimism.core.transactions
    WHERE block_date >= DATEADD('month', -{{n_months}}, CURRENT_DATE())
    GROUP BY block_date

    UNION ALL

    SELECT block_date, COUNT(*) AS daily_transactions, 'Mint' AS chain
    FROM mint.core.transactions
    WHERE block_date >= DATEADD('month', -{{n_months}}, CURRENT_DATE())
    GROUP BY block_date

    UNION ALL

    SELECT block_date, COUNT(*) AS daily_transactions, 'Mode' AS chain
    FROM mode.core.transactions
    WHERE block_date >= DATEADD('month', -{{n_months}}, CURRENT_DATE())
    GROUP BY block_date
)

SELECT 
    block_date AS date, 
    SUM(CASE WHEN chain = 'Zora' THEN daily_transactions ELSE 0 END) AS zora_transactions,
    SUM(CASE WHEN chain = 'OP Mainnet' THEN daily_transactions ELSE 0 END) AS op_mainnet_transactions,
    SUM(CASE WHEN chain = 'Mint' THEN daily_transactions ELSE 0 END) AS mint_transactions,
    SUM(CASE WHEN chain = 'Mode' THEN daily_transactions ELSE 0 END) AS mode_transactions
FROM 
    superchain_transactions
GROUP BY 
    block_date
ORDER BY 
    block_date;

Resultado:

• 94 filas / 2.39 KB en 1,038 ms

Gráfico recomendado: Barras apiladas

• Eje X: date
• Eje Y: daily_transactions
• Diferenciación: Por blockchain (Zora, OP Mainnet, Mint, Mode)

Enlace del Widget Optimizado en TokenFlow

Caso de Uso 2: Direcciones Activas

Consulta sin optimizar:

WITH superchain_calls AS (
    SELECT *, 'Zora' AS chain FROM zora.core.calls
    UNION ALL 
    SELECT *, 'OP Mainnet' AS chain FROM optimism.core.calls
    UNION ALL
    SELECT *, 'Mint' AS chain FROM mint.core.calls
    UNION ALL
    SELECT *, 'Mode' AS chain FROM mode.core.calls
)

SELECT * FROM (
    SELECT chain,
           block_date AS date,
           COUNT(DISTINCT from_address) AS "Active Addresses"
    FROM superchain_calls
    WHERE block_date >= DATEADD('month', -{{n_months}}, CURRENT_DATE())
    GROUP BY chain, block_date
) 
PIVOT (
    MAX("Active Addresses") FOR chain IN ('Zora', 'OP Mainnet', 'Mint', 'Mode')
) AS p(date, zora, "OP Mainnet", mint, mode)
ORDER BY date;

Resultado:

• 94 filas / 2.39 KB en 21,207 ms

Consulta optimizada:

WITH superchain_calls AS (
    SELECT block_date, COUNT(DISTINCT from_address) AS active_addresses, 'Zora' AS chain
    FROM zora.core.calls
    WHERE block_date >= DATEADD('month', -{{n_months}}, CURRENT_DATE())
    GROUP BY block_date

    UNION ALL

    SELECT block_date, COUNT(DISTINCT from_address) AS active_addresses, 'OP Mainnet' AS chain
    FROM optimism.core.calls
    WHERE block_date >= DATEADD('month', -{{n_months}}, CURRENT_DATE())
    GROUP BY block_date

    UNION ALL

    SELECT block_date, COUNT(DISTINCT from_address) AS active_addresses, 'Mint' AS chain
    FROM mint.core.calls
    WHERE block_date >= DATEADD('month', -{{n_months}}, CURRENT_DATE())
    GROUP BY block_date

    UNION ALL

    SELECT block_date, COUNT(DISTINCT from_address) AS active_addresses, 'Mode' AS chain
    FROM mode.core.calls
    WHERE block_date >= DATEADD('month', -{{n_months}}, CURRENT_DATE())
    GROUP BY block_date
)

SELECT 
    block_date AS date, 
    SUM(CASE WHEN chain = 'Zora' THEN active_addresses ELSE 0 END) AS zora_active_addresses,
    SUM(CASE WHEN chain = 'OP Mainnet' THEN active_addresses ELSE 0 END) AS op_mainnet_active_addresses,
    SUM(CASE WHEN chain = 'Mint' THEN active_addresses ELSE 0 END) AS mint_active_addresses,
    SUM(CASE WHEN chain = 'Mode' THEN active_addresses ELSE 0 END) AS mode_active_addresses
FROM 
    superchain_calls
GROUP BY 
    block_date
ORDER BY 
    block_date;

Resultado:

• 94 filas / 8.63 KB en 11,231 ms

Gráfico recomendado: Barras apiladas

• Eje X: date
• Eje Y: active_addresses
• Diferenciación: Por blockchain (Zora, OP Mainnet, Mint, Mode)

Enlace del Widget Optimizado en TokenFlow

---

Enfoque de Agregación Temprana

En las consultas optimizadas, implementé el enfoque de agregación temprana, realizando operaciones de agregación como conteos y sumas directamente en las subconsultas de cada blockchain antes de combinar los resultados. Esto contrasta con el enfoque sin optimizar, donde primero se unían los datos de todas las blockchains y luego se realizaban las agregaciones. Este cambio ofrece varios beneficios:

1. Menor carga de procesamiento: Al contar transacciones o direcciones activas en cada subconsulta, se reduce el tamaño del conjunto de datos procesado posteriormente, minimizando la carga de memoria y procesamiento.

2. Uso eficiente de índices: Filtrar y agregar en la fuente permite que las bases de datos utilicen mejor sus índices, acelerando las operaciones de agregación.

3. Reducción del tamaño de datos combinados: En lugar de combinar grandes volúmenes de datos sin procesar, las consultas optimizadas combinan solo los resultados agregados, reduciendo significativamente el tamaño del conjunto de datos final.

Beneficios Generales de las Optimizaciones Aplicadas

Las optimizaciones no solo mejoraron el rendimiento de cada consulta, sino que también hicieron el código más limpio, escalable y fácil de entender. Los beneficios clave incluyen:

1. Escalabilidad: Las consultas están diseñadas para ser escalables, agrupando y filtrando solo los datos necesarios, lo que mejora el rendimiento en conjuntos de datos grandes.

2. Claridad en la visualización: Las columnas diferenciadas por blockchain facilitan la generación de gráficos apilados y la visualización comparativa de métricas como transacciones, direcciones activas, contratos desplegados y fees pagadas.

3. Reducción del tiempo de ejecución: Como se observó en los casos de uso, el tiempo de ejecución se redujo drásticamente en las consultas optimizadas, permitiendo ejecutar análisis más complejos en menos tiempo y liberando recursos para otros procesos.

---

Caso de Uso 3: Excepción en la Optimización

En este caso, analicé los contratos desplegados en las diferentes blockchains de la Superchain. Al aplicar las mismas optimizaciones, la mejora en el rendimiento no fue tan significativa, convirtiéndose en una excepción interesante.

Consulta sin optimizar:

WITH superchain_calls AS (
    SELECT *, 'Zora' AS chain FROM zora.core.calls WHERE call_type IN ('CREATE', 'CREATE2')
    UNION ALL
    SELECT *, 'OP Mainnet' AS chain FROM optimism.core.calls WHERE call_type IN ('CREATE', 'CREATE2')
    UNION ALL
    SELECT *, 'Mint' AS chain FROM mint.core.calls WHERE call_type IN ('CREATE', 'CREATE2')
    UNION ALL
    SELECT *, 'Mode' AS chain FROM mode.core.calls WHERE call_type IN ('CREATE', 'CREATE2')
)

SELECT * FROM (
    SELECT chain,
           block_date AS date,
           COUNT(*) AS "Contracts Deployed"
    FROM superchain_calls
    WHERE date >= DATEADD('month', -{{n_months}}, CURRENT_DATE())
    GROUP BY chain, block_date
) 
PIVOT (
    MAX("Contracts Deployed") FOR chain IN ('Zora', 'OP Mainnet', 'Mint', 'Mode')
) AS p(date, zora, "OP Mainnet", mint, mode)
ORDER BY date;

Resultado:

• 94 filas / 2.39 KB en 30,429 ms

Consulta optimizada:

WITH superchain_calls AS (
    SELECT block_date, COUNT(*) AS contracts_deployed, 'Zora' AS chain
    FROM zora.core.calls
    WHERE call_type IN ('CREATE', 'CREATE2')
    AND block_date >= DATEADD('month', -{{n_months}}, CURRENT_DATE())
    GROUP BY block_date

    UNION ALL

    SELECT block_date, COUNT(*) AS contracts_deployed, 'OP Mainnet' AS chain
    FROM optimism.core.calls
    WHERE call_type IN ('CREATE', 'CREATE2')
    AND block_date >= DATEADD('month', -{{n_months}}, CURRENT_DATE())
    GROUP BY block_date

    UNION ALL

    SELECT block_date, COUNT(*) AS contracts_deployed, 'Mint' AS chain
    FROM mint.core.calls
    WHERE call_type IN ('CREATE', 'CREATE2')
    AND block_date >= DATEADD('month', -{{n_months}}, CURRENT_DATE())
    GROUP BY block_date

    UNION ALL

    SELECT block_date, COUNT(*) AS contracts_deployed, 'Mode' AS chain
    FROM mode.core.calls
    WHERE call_type IN ('CREATE', 'CREATE2')
    AND block_date >= DATEADD('month', -{{n_months}}, CURRENT_DATE())
    GROUP BY block_date
)

SELECT 
    block_date AS date, 
    SUM(CASE WHEN chain = 'Zora' THEN contracts_deployed ELSE 0 END) AS zora_contracts_deployed,
    SUM(CASE WHEN chain = 'OP Mainnet' THEN contracts_deployed ELSE 0 END) AS op_mainnet_contracts_deployed,
    SUM(CASE WHEN chain = 'Mint' THEN contracts_deployed ELSE 0 END) AS mint_contracts_deployed,
    SUM(CASE WHEN chain = 'Mode' THEN contracts_deployed ELSE 0 END) AS mode_contracts_deployed
FROM 
    superchain_calls
GROUP BY 
    block_date
ORDER BY 
    block_date;

Resultado:

• 94 filas / 9.36 KB en 29,781 ms

Enlace del Widget Optimizado en TokenFlow

Análisis de la Excepción

A pesar de aplicar las mismas técnicas de optimización, la mejora fue mínima (de 30,429 ms a 29,781 ms). Posibles razones:

1. Tamaño de los datos: El volumen de datos era significativamente menor, por lo que la optimización no generó una mejora notable en el tiempo de ejecución.

2. Complejidad del cálculo: El conteo de contratos desplegados (COUNT(*)) ya se manejaba eficientemente, y evitar el uso de PIVOT no aportó una mejora significativa.

3. Procesamiento de tipos de transacción: El filtro por call_type IN ('CREATE', 'CREATE2') es directo y no introduce sobrecarga, limitando las posibilidades de optimización.

---

Conclusión Final del Análisis

En este artículo, exploré las optimizaciones aplicadas a consultas SQL para analizar métricas clave en las blockchains de la Superchain, enfocándome en transacciones diarias, direcciones activas y contratos desplegados. En la mayoría de los casos, el enfoque de agregación temprana y otras técnicas optimizaron significativamente el rendimiento.

Sin embargo, el análisis de contratos desplegados presentó una excepción, recordándonos que las optimizaciones no siempre tendrán el mismo impacto en todos los contextos y tipos de consultas.

Resumen de los Pilares y Resultados

• Transacciones Diarias: Reducción del tiempo de ejecución de 5,879 ms a 1,038 ms (84% de mejora).
• Direcciones Activas: Reducción de 21,207 ms a 11,231 ms (47% de mejora).
• Contratos Desplegados (Excepción): Reducción mínima de 30,429 ms a 29,781 ms (2% de mejora).

Optimizaciones Aplicadas - Resumen

1. Selección de columnas específicas.
2. Filtrado por fecha optimizado.
3. Uso de SUM con CASE para evitar PIVOT.
4. Combinación eficiente de datos con UNION ALL.

Conclusión General

Las optimizaciones aplicadas, como la agregación temprana y la selección cuidadosa de columnas, demostraron ser efectivas en la mayoría de los casos. Estas mejoras no solo reducen el tiempo de ejecución, sino que también hacen las consultas más legibles y mantenibles. Aunque siempre habrá casos donde el impacto sea menor, este análisis evidencia cómo pequeños ajustes pueden generar mejoras significativas en el rendimiento en entornos con grandes volúmenes de datos como el análisis de blockchains.