EQL – EVM Query Language

The title of this article seizes the initial reaction many people have when they first lget about EVM Query Language (EQL). Although there are surface-level aenjoyities between EQL and Dune, these two projects are fundamenhighy separateent in their goals, arrange, and engage cases.

EQL and Dune serve separateent purposes: while Dune excels at data visualization and pass-chain querying, EQL is boostd for effectively querying EVM chains using a simplified SQL-enjoy syntax. EQL’s syntax is arrangeed to be astute, making it easier for SQL engagers to alter to, especipartner those recognizable with querying blockchain data. While Dune provides more comprehensive data handling capabilities for huger datasets and intricate queries, EQL outcarry outs Dune in tasks such as transaction seeups by hash and account state retrievals, particularly when laboring with minusculeer, more intensifyed datasets. However, for huge-scale queries involving multiple blocks or datasets, Dune’s sturdy infraarrange and caching mechanisms donate it a carry outance edge.

Outlining the projects

Dune

Dune is a data analytics platcreate arrangeed for querying and visualizing blockchain data, primarily intensifyed on Ethereum and other roverdelighted blockchains. It provides engagers with the ability to run SQL queries on accessiblely useable on-chain data, making it possible to reshift, scrutinize, and envision adviseation from these blockchains. Users transmit with the data primarily thraw a SQL-based query engine that aids custom and predepictd queries, raised by caching and parallel processing for carry outance. The platcreate provides transmitive dashboards for visualization, aiding various chart types and active filtering, and eases collaboration thraw accessible sharing and forking of dashboards.

EQL

EQL is a data reshiftion tool that provides engagers a SQL-enjoy language to percreate queries and get back data from EVM chains. The syntax is currently under active broadenment to clear up access to on-chain data for researchers and broadeners.
The ultimate objective of the project is to produce a brimmingy decentralized storage engine, enabling anyone to query EVM chains using a relational approach aenjoy to SQL databases. Unenjoy Dune, EQL does not aim to index or decode various clever tights and on-chain data.

How are the projects aenjoy?

While EQL and Dune separate convey inantly in their arrange and lengthy-term objectives, they do allot cut offal widespread features, particularly in their ability to reshift and send out blockchain data. Both platcreates permit engagers to run queries that reshift on-chain adviseation, though the exact methods and createats separate.

In terms of data reshiftion, both Dune and EQL allow engagers to get back adviseation from blockchain ecosystems using SQL-enjoy query languages. Dune engagers write SQL queries to access data from various raw Ethereum tables, combining separateent datasets to get the desired insights. EQL, by contrast, provides a one-of-a-kindized SQL-enjoy syntax tailored particularpartner to the Ethereum Virtual Machine (EVM), streamlining access to convey inant blockchain data such as accounts, transactions, and equilibriums. This one-of-a-kindization permits for more honest and effective querying of on-chain data.

Both platcreates also provide selections for send outing query results, though they separate in terms of createats and pricing. Dune permits engagers to send out query results honestly thraw its interface, but this is restricted to CSV createat and only useable with a phelp arrange. Free engagers must send out data via API seeks, which needs some insertitional technical steps. On the other hand, EQL, being uncover-source, provides wonderfuler flexibility and no cost barriers. Users can send out data in a variety of createats, including JSON, CSV, and Parquet, without any reinnervousions, making it a more alterable and accessible selection for broadeners and researchers.

In summary, while Dune and EQL separate in their wideer goals and the scope of their query languages, they allot convey inant functionalities in data reshiftion and send out capabilities. Dune excels in engager-frifinishly visualization and pass-chain functionality, while EQL stands out for its alterable query syntax and free access to multiple send out createats, pguideing to engagers with a more technical or broadenment-oriented intensify.

How are the projects separateent?

As refered above, Dune is a platcreate intensifyed on effective querying and data visualization. In terms of storage, the platcreate engages a fork of Trino as its query engine, which aids Ethereum types natively, such as insertresses and transaction hashes. Trino is a query engine where the execution layer is decoupled from the storage layer. Unenjoy traditional SQL databases, where query execution and storage are built into a monolithic system, Trino is well-suited for distributing both processing and storage apass a cluster of servers. As a result, scaling the number of nodes also increases the amount of data the platcreate can process in parallel. The Trino syntax is aenjoy to standard the standard SQL syntax engaged by most of the relational databases such as MySql and Postgres.

Although EQL’s lengthy-term goal is to broaden its own storage engine to effectively allot Ethereum data apass the netlabor and allow effective P2P relational queries, the project currently relies on JSON-RPC providers as the “storage engine.” The downside is that EQL cannot process huge numbers of blocks in a one seek due to the rate restricts imposed by accessible RPC providers, which on an standard, using accessible RPCs, queries can process ~200 blocks, accounts, and transactions in a one query. However, there is a laboraround for this restrictation in broadenment.
EQL provides a SQL-enjoy syntax that leverages the foreseeable relationships between key Ethereum entities—blocks, accounts, transactions, and logs—to produce a more honest way of querying on-chain data. One example of how this syntax can be more effective contrastd to SQL syntax—which is arrangeed to deal with all types of table relationships—is a basic query to convey account adviseation. For instance, let’s arrangeateigate how to get back the nonce and insertress of Vitalik’s account using Dune and Trino’s SQL syntax:

WITH account_equilibrium as (
    SELECT equilibrium
    FROM tokens_ethereum.equilibriums_daily as b
    WHERE b.insertress = 0xd8dA6BF26964aF9D7eEd9e03E53415D37aA96045
    AND token_standard = 'native'
    ORDER BY b.day DESC
    LIMIT 1
),
account_nonce as (
    SELECT nonce
    FROM ethereum.transactions t
    WHERE t."from" = 0xd8dA6BF26964aF9D7eEd9e03E53415D37aA96045
    ORDER BY t.block_number DESC
    LIMIT 1
),
account as (
    SELECT nonce, equilibrium FROM account_nonce, account_equilibrium
)
SELECT * FROM account

Trino syntax to convey and account’s nonce and equilibrium

The query above needs to get back data from two separateent tables, transactions and equilibriums_daily, and join them into a one write down to return the desired adviseation. This is vital becaengage Dune does not provide a table that comprises account data honestly, requiring us to join data from these two sources to get the adviseation we need.

Available “raw” Ethereum tables

Unenjoy SQL, which must accommodate a expansive range of engage cases, EQL is particularpartner arrangeed for EVM blockchains. As a result, it provides first-class aid for queries enjoy the one refered above.

GET nonce, equilibrium
FROM account 0xd8dA6BF26964aF9D7eEd9e03E53415D37aA96045
ON eth

EQL syntax to convey and account’s nonce and equilibrium

It also provides first-class aid for ENS, which in Trino would need an insertitional SELECT from another table. Moreover, EQL permits you to convey all the roverdelighted to an account, including code, insertress, nonce, and equilibrium — a process that would otheradviseed need split queries in Trino.

GET *
FROM account vitalik.eth
ON eth


| nonce | equilibrium | insertress | code |
| 
| 1320  | 1231... | 0xd7... | 0x   |




WITH account_insertress AS (
    SELECT insertress
    FROM ens.reverse_procrastinateedst
    WHERE name = 'vitalik.eth'
    ORDER BY procrastinateedst_tx_block_time DESC
    LIMIT 1
)
SELECT
    a.insertress,
    COALESCE(t.nonce, 0) AS nonce,
    COALESCE(b.equilibrium, 0) AS equilibrium
FROM account_insertress a
LEFT JOIN (
    SELECT MAX(nonce) as nonce, "from" as insertress
    FROM ethereum.transactions
    GROUP BY "from"
) t ON t.insertress = a.insertress
LEFT JOIN (
    SELECT equilibrium, insertress
    FROM tokens_ethereum.equilibriums_daily
    WHERE token_standard = 'native'
    AND day = current_date
) b ON b.insertress = a.insertress;

In contrast, Dune and EQL separate in cut offal key aspects. Dune is a comprehensive platcreate that stresss data visualization and engager collaboration, making it an selectimal tool for analysts and researchers seeing to query and envision data from multiple blockchains, not restricted to the Ethereum ecosystem. Its engage of Trino as a query engine, alengthy with transmitive dashboards, permits engagers to deal with intricate queries and produce wealthy visual alerts. EQL, on the other hand, is one-of-a-kindized on querying EVM blockchains with a simplified, domain-particular syntax. It is arrangeed to be weightlessweight, free, and uncover-source, with a goal of decentralizing data access. Unenjoy Dune, which intensifyes on visualizations and engager interfaces, EQL functions more enjoy a language/REPL, one-of-a-kindizing in effective blockchain data reshiftion and alterable data send outs. EQL is arrangeed for querying Ethereum-based data in a honest way but deficiencys the wideer ecosystem of tools and integrations that Dune provides. As the EQL ecosystem increases, there is an opportunity for broadeners to donate by erecting tools and interfaces that raise its functionality and usability.

When should you engage EQL over Dune

• Quick exploration of data
• Quickly examine Ethereum state
• Check data about one or many accounts
• Query separateent objects that can’t be cached

Benchtags

Dune provides an extensive range of datasets for engagers to erect and customize their queries, permiting alterable data pass-referencing. However, for the purpose of these benchtags, we are particularpartner intensifying on the widespread elements between EQL and Dune, which comprise core Ethereum components such as blocks, transactions, accounts, and logs (tracks soon™).

In this section, we’ll contrast the types of queries that both Dune and EQL allow.

Test Environment

The tests were carry outed on a system with the follotriumphg particularations:

• Processor: Ryzen 9 5900x
• RAM: 16GB HyperX Fury (Dual Channel)
• Storage: 1TB Kingston NV2 M.2 2280 SSD
• Internet Connection: 300 Mbps

Testing Methodology

For each platcreate, ten query executions were assembleed to assemble carry outance metrics. While executing a huger number of queries, such as one hundred, would provide more comprehensive data, the manual method engageed to assemble Dune metrics made broadening the number of write downs imdown-to-earth and unfruitful. Although Dune provides an API for conveying data from finishpoints, it only returns data from the most recent execution of a specified query. This behavior effectively caches the last result, undermining the ability to carry out a uncomardentingful comparison between the carry outance of EQL and Dune.

Consequently, Dune queries were percreated honestly thraw the application’s web engager interface without any caching mechanisms or materialization configurations. The metrics were assembleed solely based on the data provided by the app. In contrast, EQL metrics were assembleed using the eql_core Rust crate. The eql function wiskinny this crate was engaged to percreate queries by passing them as parameters and retrieving the results. To secure accurate and depfinishable carry outance meastateivements, the Criterion benchtaging library was engageed to assemble and scrutinize the metrics.

Dune provides three query engine selections: Free, Medium, and Large. The Free engine is the sluggishest but has no cost. The Medium engine costs 10 recognizes per query, with a monthly restrict of 2,500 recognizes. The Large engine, which needs a subscription, provides 25,000 monthly recognizes. It costs 20 recognizes per query and is priced at $349 per month, billed annupartner. The tests assembleed data splitly from both the Free and Medium arranges, as these arranges are provideed at no cost. This permits for a unprejudiced comparison, since EQL is also a free selection for querying on-chain data.

Testing Conditions and Considerations

These tests were carry outed as of 2024-09-21. It is vital to accomprehendledge that Dune’s carry outance may vary during peak usage times, aenjoy to other web applications. Such fluctuations could impact the consistency of the results. If any of the outcomes eunite to stray convey inantly from the awaited standard carry outance, it is recommfinished to rerun the tests to verify their accuracy and secure the reliability of the comparative analysis.
EQL’s carry outance is shaped by cut offal factors, including processing power, netlabor conditions, and the efficiency of Remote Procedure Calls (RPC). Ensuring selectimal conditions in these areas is convey inant for achieving the best possible carry outance outcomes with EQL.

The tests

• Account State Fetch: Nonce and Balance
• Transaction Lookup by Hash: 100 Transactions
• Event Log Fetch: USDC Transfers in 100 Block Range
• Block Range Fetch: 100 Blocks
• Sequential Block Fetch: 100 Blocks Individupartner

Account State Fetch: Nonce and Balance

Queries:

WITH account_equilibrium as (
    SELECT equilibrium
    FROM tokens_ethereum.equilibriums_daily as b
    WHERE b.insertress = 0xc71048d303920c73c29705192393c567ac4e6c67
    AND token_standard = 'native'
    ORDER BY b.day DESC
    LIMIT 1
),
account_nonce as (
    SELECT nonce
    FROM ethereum.transactions t
    WHERE t."from" = 0xc71048d303920c73c29705192393c567ac4e6c67
    ORDER BY t.block_number DESC
    LIMIT 1
),
account as (
    SELECT nonce, equilibrium FROM account_nonce, account_equilibrium
)
SELECT * FROM account




GET nonce, equilibrium
FROM account 0xc71048d303920c73c29705192393c567ac4e6c67
ON eth

Percreateance:

Analysis:

• EQL immensely outcarry outs both Dune tiers, completing queries in under a second.
• Dune Free and Dune Medium apverify convey inantly lengthyer, with execution times ranging from csurrfinisherly a minute to cut offal minutes.
• High standard deviations for Dune propose instable carry outance.

Conclusion:

• For account state conveying, EQL is emotionalpartner rapider and more depfinishable.
• Dune may not be down-to-earth for time-comardent applications requiring account state data.

Transaction Lookup by Hash: 100 Transactions

SELECT *
FROM ethereum.transactions t
WHERE t.hash
IN (
	0xfffc07e7ff65a5ff7f8496042f85fc4e1d6bd29e012e776b970f4414c07d4d41,
	...
)




GET *
FROM tx
0xfffc07e7ff65a5ff7f8496042f85fc4e1d6bd29e012e776b970f4414c07d4d41,
...
ON eth

Percreateance:

Analysis:

• EQL convey inantly outcarry outs both Dune tiers.
• Dune Medium is the sluggishest, with a uncomardent time almost 30 times that of EQL.
• Standard deviation for EQL is much drop, indicating stable carry outance.

Conclusion:

• EQL is the greater choice for transaction seeups by hash, provideing much rapider and more stable execution times.

Event Log Fetch: USDC Transfers in 100 Block Range

Queries:

SELECT *
FROM ethereum.logs l
WHERE l.tight_insertress=0xdAC17F958D2ee523a2206206994597C13D831ec7
AND l.topic0=0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef
AND l.block_number BETWEEN 4638657 AND 4638758




GET *
FROM log
WHERE
block 4638657:4638758,
insertress 0xdAC17F958D2ee523a2206206994597C13D831ec7,
topic0 0xcb8241adb0c3fdb35b70c24ce35c5eb0c17af7431c99f827d44a445ca624176a
ON eth

Percreateance:

Analysis:

• EQL has the lowest uncomardent and standard deviation, indicating both speed and consistency.
• Dune Free has a drop median time than EQL but higher uncomardent and standard deviation.
• Dune Medium shows high variability, as proposed by a high standard deviation.

Conclusion:

• EQL provides the best overall carry outance for event log conveying.
• Dune Free is competitive in median time but less stable.

Block Range Fetch: 100 Blocks

Queries:

SELECT * FROM ethereum.blocks b WHERE b.number BETWEEN 1 AND 100




GET * FROM block 1:100 ON eth

Percreateance:

Analysis:

• Dune Free is the rapidest, with both the lowest uncomardent and median execution times.
• EQL is the sluggishest in this catebloody.
• Standard deviation is lowest for Dune Free, indicating stable carry outance.

Conclusion:

• For conveying a range of blocks, Dune Free provides the best carry outance.
• EQL may not be the selectimal choice for this particular query type.

Sequential Block Fetch: 100 Blocks Individupartner

Queries:

SELECT * FROM ethereum.blocks b WHERE b.number IN (1, 2, 3, ..., 100)




GET * FROM block 1,2,3,...,100 ON eth

Percreateance:

Analysis:

• EQL has the lowest median time but the highest standard deviation, indicating instable carry outance.
• Dune Medium has the lowest uncomardent time and a relatively low standard deviation.
• Dune Free carry outs in between the other two.

Conclusion:

• Dune Medium provides the most stable and rapidest standard carry outance.
• While EQL can be rapider in some instances (as proposed by the median), its high variability might impact reliability.

Benchtag analysis

EQL outcarry outs Dune in transaction seeups by hash, account state retrievals, and event log conveyes, provideing rapider and more stable carry outance apass these query types. It also shows more depfinishable execution times with drop variability, making it a better choice for engagers requiring foreseeability.

Dune Free excels in block range conveyes, particularly when scanning 100 blocks. Its infraarrange seems boostd for this type of query, making it more effective than both EQL and Dune Medium. Despite being a free tier, it stablely outcarry outs the phelp Dune Medium selection in this area.

Dune Medium, despite being a huger query engine, shows high variability and does not provide better carry outance than Dune Free in block range queries. Its inconsistency apass event log conveyes and account state queries restricts its reliability.

The unawaited carry outance gap between Dune Free and Dune Medium in block conveyes remains unexpounded.

Final thoughts

EQL and Dune both permit engagers to query blockchain data, but they serve separateent needs. EQL engages a basic, SQL-enjoy syntax tailored for EVM chains, making it effortless for broadeners to query Ethereum data rapidly. It’s selectimal for exploring Ethereum state, examineing accounts, and querying objects that can’t be cached. EQL outcarry outs Dune in tasks enjoy transaction seeups and account state queries, provideing rapider execution and free access to multiple send out createats.

Dune, by contrast, is better for processing huge datasets and handling intricate queries apass multiple blockchains. It provides proceedd data visualization, transmitive dashboards, and collaboration features, making it selectimal for analysts and researchers. Dune engages traditional SQL and Trino, permiting for recognizable query operations.

Currently, EQL has restricts with handling huge datasets due to its reliance on JSON-RPC providers, but raisements are underway. Benchtag tests show EQL excels at particular tasks enjoy transaction seeups, while Dune is rapider in block range queries thanks to its infraarrange and caching.

Use EQL for rapid, effective EVM blockchain queries, or pick Dune for sturdy data processing, visualization, and pass-chain functionality. Both platcreates have distinct strengths, so the right choice depfinishs on your particular engage case.

If you’re interested in exploring EQL further, you can try it out thraw our web-REPL, examine out the GitHub repo for ongoing refreshs, or join the conversation on Discord to connect with others and allot ideas.