Luabase vs Dune Speed Comparison

Blockchains generate a lot of data. Like, A LOT of data. Unsurprisingly querying terabytes and billions of rows of blockchain data is not only computationally intensive but also time intensive, especially if the query includes complex SQL joins or aggregations. In this post, we compare query speeds for two of the most popular SQL-based blockchain analytics products on the market, Luabase and Dune.

Before we begin, a little bit about both products' infrastructure (more in-depth feature comparison here):

Luabase

• Query engine built largely on top of Clickhouse
• Multi-cluster Clickhouse instances for better load balancing during times of high/complex query demand

Dune

• Dune V2 engine (in beta) is built on top of Spark for better big data processing
• Dune V1 engine (where most of Dune's data and queries still run on) is built with Postgres

Queries used for comparison are taken from public queries created by other Dune users and are copied verbatim into Luabase except where column names or SQL syntax differ. When comparing query speed between the two platforms, we use Dune's V2 engine whenever possible. Dune V1 will be used when data is unavailable in V2 (e.g. Polygon is not available on V2 at the time of this post).

Note: these results were obtained at the time of publication; future results may vary as data volume , query load, and/or platform infrastructure changes.

Query 1: Distinct Depositors Into Ethereum Staking Contract

The first query we look at counts the number of unique addresses that have deposited into the ETH2 staking contract. The query on Luabase looks like:

SELECT 
	COUNT(distinct from_address) AS stakers_count
FROM ethereum.traces
WHERE to_address = lower('0x00000000219ab540356cBB839Cbe05303d7705Fa')
AND status = 1
AND value > 0

Luabase

• Query time: ~48.4 seconds
• Query link

Dune

• Query time: ~1 minute
• Engine: Dune v2
• Query link

Query 2: Ethereum Daily Gas Used

The next query is a daily aggregation of total transactions, unique transacting addresses, gas used, average gas used, and average gas price on Ethereum. The query in Luabase looks like:

select
    date_trunc('day', block_timestamp) as dt,
    count(hash) as num_txs,
    count(distinct from_address) as num_addr,
    sum(receipt_gas_used) as gas_used,
    avg(receipt_gas_used) as avg_gas_used,
    avg(gas_price) as avg_gas_price
from ethereum.transactions
where 
    receipt_status = 1
    and
block_number >= 10000000
group by date_trunc('day', block_timestamp)

Luabase

• Query time: ~1 minute
• Query link

Dune

• Query time: ~2 minutes
• Engine: Dune v2
• Query link

Query 3: Avalanche Daily Gas Used

Same query as the previous one but for Avalanche C-Chain. Query on Luabase:

with transactionn_gas_used as (
    select block_timestamp, gas_price, receipt_gas_used, gas_price * receipt_gas_used as gas_amount, hash
    from avalanche.transactions
)

select date_trunc('day', block_timestamp) as block_date,
    count(*) as transaction_count,
    avg(gas_price) / 1e9 as average_gas_price,
    sum(receipt_gas_used) / 1e9 as sum_gas_used,
    avg(receipt_gas_used) as average_gas_used,
    sum(gas_amount) / 1e18 as sum_gas_amount,   -- Not reiliable
    avg(gas_amount) / 1e18 as average_gas_amount    -- Not reiliable
from transactionn_gas_used
group by block_date
order by block_date

Luabase

• Query time: ~2.6 seconds
• Query link

Dune

• Query time: ~19 seconds
• Engine: Dune v2
• Query link

Query 4: Daily Active Polygon Addresses

This query looks at number of unique addresses per day that had an outgoing transaction. Luabase query:

with log as (
    select
        date_trunc('month', block_timestamp) as month,
        count(from_address) as active_wallets
    from polygon.transactions
    where receipt_status = 1
    group by month
)
select * from log
order by 1 desc

Luabase

• Query time: ~11.7 seconds
• Query link

Dune

• Query time: ~7 minutes
• Engine: Dune v1
• Query link

Query 5: Distribution of Polygon Transaction Volume (USD) in Last 30 Days

This query is a bit of a mouthful to understand but it essentially buckets Polygon transaction amounts in $500 increment buckets (e.g. 0-500, 500-1000, 1000-1500, etc) and counts the number of transactions in each bucket.

This query involves a join with a USD prices table. The Luabase query looks like:

with eth_txns as
(
SELECT
  value/1e18 as value_matic, block_number, block_timestamp
FROM
  polygon.transactions
WHERE
  value > 0
  AND block_timestamp > dateadd(day, -30, now())
  AND receipt_status = 1

)

SELECT
    SUM(CASE WHEN value_matic * price_close < 100 THEN value_matic * price_close ELSE 0 END) AS less_than_usd_100,
    SUM(CASE WHEN value_matic * price_close BETWEEN 100 AND 500 THEN value_matic * price_close ELSE 0 END) AS usd_100_to_500,
    SUM(CASE WHEN value_matic * price_close BETWEEN 501 AND 1000 THEN value_matic * price_close ELSE 0 END) AS usd_501_to_1000,
    SUM(CASE WHEN value_matic * price_close BETWEEN 1001 AND 5000 THEN value_matic * price_close ELSE 0 END) AS usd_1001_to_5000,
    SUM(CASE WHEN value_matic * price_close BETWEEN 5001 AND 10000 THEN value_matic * price_close ELSE 0 END) AS usd_5001_to_10000,
    SUM(CASE WHEN value_matic * price_close BETWEEN 10001 AND 50000 THEN value_matic * price_close ELSE 0 END) AS usd_10001_to_50000,
    SUM(CASE WHEN value_matic * price_close BETWEEN 50001 AND 100000 THEN value_matic * price_close ELSE 0 END) AS usd_50001_to_100000,
    SUM(CASE WHEN value_matic * price_close BETWEEN 100001 AND 500000 THEN value_matic * price_close ELSE 0 END) AS usd_100001_to_500000,
    SUM(CASE WHEN value_matic * price_close BETWEEN 500001 AND 1000000 THEN value_matic * price_close ELSE 0 END) AS usd_500001_to_1000000,
    SUM(CASE WHEN value_matic * price_close > 1000000 THEN value_matic * price_close ELSE 0 END) AS greater_than_usd_1000000
FROM eth_txns

LEFT JOIN
    (SELECT timestamp, price_close from prices.matic_usd
        WHERE timestamp > dateadd(day, -30, now())
    ) AS prices
     on toStartOfMinute(eth_txns.block_timestamp) = timestamp

Luabase

• Query time: ~2.3 seconds
• Query link

Dune

• Query time: ~2 minutes
• Engine: Dune v1
• Query link

Query 6: Uniswap v3 DAI-USDC Pool Monthly Average Transaction Fee

This query looks at the monthly average transaction fee for interacting with Uniswap v3's DAI-USDC liquidity pool. This query involves joining two Ethereum tables, traces and transactions.

Luabase query:

with swaps as (
    select distinct transaction_hash from ethereum.traces where 
    from_address = lower('0x5777d92f208679DB4b9778590Fa3CAB3aC9e2168')
    or to_address = lower('0x5777d92f208679DB4b9778590Fa3CAB3aC9e2168')
)

select 
    toStartOfMonth(block_timestamp) as month,
    avg(gas_price) / 1e18 as avg_gas_per_swap
from transactions as t 
inner join swaps on t.hash = swaps.transaction_hash
group by month

Luabase

• Query time: ~32 seconds
• Query link

Dune

• Query time: ~20 minutes
• Engine: Dune v2
• Query link

Summary

The table below summarizes the results:

	Query 1	Query 2	Query 3	Query 4	Query 5	Query 6
Luabase	48.4 seconds	1 minute	2.6 seconds	11.7 seconds	2.3 seconds	32 seconds
Dune v2	1 minute	2 minutes	19 seconds	n/a	n/a	20 minutes
Dune v1	n/a	n/a	n/a	7 minutes	2 minutes	n/a

If you would like a more in-depth comparison of Luabase, Dune, and other blockchain data providers, check out our other post here. Lastly, if you are looking for fast blockchain SQL query results AND fast data exports, Luabase also offers an API to easily export results from any query.