Skip to main content
This guide provides practical do’s and don’ts specifically tailored to DuneSQL and Dune’s architecture, allowing you to optimize credit spend.

​
Key Principles

Dune’s query engine is optimized for blockchain data analysis with time-partitioned tables and columnar storage. Understanding the DuneSQL architecture will help you write queries that execute faster and consume fewer resources.

​
✅ DO’s: Query Optimization Best Practices

​
1. Leverage Time-Based Partitioning

Dune partitions most tables by block_date or by block_time Check on the data explorer (left-side panel) to see which fields are used as partitions. Always include time filters to enable partition pruning. Cross-chain tables: Large cross-chain tables like tokens.transfers, dex.trades, and evms.erc20_evt_transfers are most often also partitioned by blockchain in addition to time. Wherever possible, specify the blockchain filter along with time filters to dramatically reduce the amount of data scanned. 
-- ✅ GOOD: Filters by block_date to enable partition pruning
SELECT 
    hash,
    "from",
    "to",
    value
FROM base.transactions
WHERE block_date >= TIMESTAMP '2025-09-01 00:00:00'
  AND block_date < TIMESTAMP '2025-10-02 00:00:00'
  AND "to" = 0x827922686190790b37229fd06084350E74485b72

-- ✅ EXCELLENT: Filters by both blockchain and time for cross-chain tables
SELECT 
    block_time,
    tx_hash,
    "from",
    "to",
    amount_usd
FROM dex.trades
WHERE blockchain = 'ethereum'
  AND block_time >= TIMESTAMP '2024-10-01'
  AND block_time < TIMESTAMP '2024-11-01'

​
2. Select Only Required Columns

Specify only the columns you need. Dune’s columnar storage makes this especially effective. 
-- ✅ GOOD: Selects only necessary columns
SELECT 
    evt_block_time,
    "from",
    "to", 
    value
FROM erc20_ethereum.evt_Transfer
WHERE contract_address = 0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48
  AND evt_block_time >= NOW() - INTERVAL '7' DAY

​
3. Use Efficient JOIN Strategies

Put time filters in the ON clause and join on indexed columns when possible. 
-- ✅ GOOD: Efficient join with time filter in ON clause
SELECT 
    t.hash,
    t.block_time,
    l.topic0,
    l.data
FROM ethereum.transactions t
INNER JOIN ethereum.logs l 
    ON t.hash = l.tx_hash
    AND t.block_date = l.block_date
    AND l.block_date >= TIMESTAMP '2024-10-01'
    AND l.block_date < TIMESTAMP '2024-10-02'
WHERE t."to" = 0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48
    AND t.block_date >= TIMESTAMP '2024-10-01'
    AND t.block_date < TIMESTAMP '2024-10-02'

​
4. Use CTEs for Complex Logic

Break complex queries into readable Common Table Expressions. 
-- ✅ GOOD: Using CTEs for better performance and readability
WITH daily_volumes AS (
    SELECT 
        block_month as trade_date,
        SUM(amount_usd) as daily_volume
    FROM dex.trades
    WHERE blockchain = 'ethereum'
      AND block_month >= TIMESTAMP '2024-09-01'     
      AND block_month < TIMESTAMP '2024-10-01'
    GROUP BY block_month
),
volume_metrics AS (
    SELECT 
        trade_date,
        daily_volume,
        AVG(daily_volume) OVER (
            ORDER BY trade_date 
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        ) as rolling_7day_avg
    FROM daily_volumes
)
SELECT * FROM volume_metrics
ORDER BY trade_date DESC

​
5. Use LIMIT with Large Result Sets

Always use LIMIT when you don’t need all results, especially for exploratory queries. 
-- ✅ GOOD: Using LIMIT for large result sets
SELECT 
    hash,
    block_time,
    gas_used,
    gas_price
FROM ethereum.transactions
WHERE block_time >= TIMESTAMP '2024-10-01'
ORDER BY gas_price DESC
LIMIT 1000

​
Use Curated Data Tables

Leverage Dune’s curated tables which are pre-computed and optimized rather than use raw logs and traces.

​
Use Materialized Views

Materialized views are a way to store the results of a query in a table that can be queried like any other table. This is useful when you have a query that takes a long time to run, as you can re-use the results without having to re-execute your whole query.

​
Query Performance Troubleshooting

  • Monitor query execution time in the Dune interface
  • Check the query plan for full table scans (run EXPLAIN ANALYZE YOUR_QUERY)
  • Consider query complexity vs. result value trade-offs

​
❌ DON’Ts: Query Anti-Patterns to Avoid

​
1. DON’T Use SELECT * on Large Tables

Avoid selecting all columns, especially on transaction and log tables. 
-- ❌ BAD: Select all columns unnecessarily
SELECT * 
FROM ethereum.transactions
WHERE block_time >= TIMESTAMP '2024-10-01'

-- ✅ GOOD: Select only needed columns
SELECT hash, from_address, to_address, value
FROM ethereum.transactions  
WHERE block_time >= TIMESTAMP '2024-10-01'

​
2. DON’T Create Unnecessary Subqueries

Avoid nested subqueries when JOINs or window functions work better. 
-- ❌ BAD: Inefficient correlated subquery
SELECT 
    b1.number,
    b1.gas_used,
    (
        SELECT AVG(b2.gas_used)
        FROM ethereum.blocks b2
        WHERE b2.time >= TIMESTAMP '2024-10-01'
          AND b2.number <= b1.number
          AND b2.number > b1.number - 100
    ) AS avg_100_blocks
FROM ethereum.blocks b1
WHERE b1.time >= TIMESTAMP '2024-10-01';

-- ✅ GOOD: Use window functions instead
WITH gas_used AS (
    SELECT 
        number,
        gas_used,
        AVG(gas_used) OVER (
            ORDER BY time
            ROWS BETWEEN 99 PRECEDING AND CURRENT ROW
        ) as avg_100_blocks
    FROM ethereum.blocks
    WHERE time >= TIMESTAMP '2024-10-01'
) 
SELECT 
    number,
    gas_used,
    avg_100_blocks
FROM gas_used

​
3. DON’T Use ORDER BY Without LIMIT on Large Results

Sorting large result sets is expensive. 
-- ❌ BAD: Ordering large result set without limit
SELECT hash, gas_price
FROM ethereum.transactions
WHERE block_time >= TIMESTAMP '2024-10-01'
ORDER BY gas_price DESC

-- ✅ GOOD: Add LIMIT when ordering
SELECT hash, gas_price
FROM ethereum.transactions
WHERE block_time >= TIMESTAMP '2024-10-01'
ORDER BY gas_price DESC
LIMIT 10000

​
Summary

Efficient DuneSQL queries on Dune require understanding the platform’s time-partitioned architecture. Focus on:
  1. Time filtering with time fields for partition pruning
  2. Column selection to leverage columnar storage
  3. Strategic joins with proper filter placement
  4. Avoiding anti-patterns that prevent optimizations
⌘I