Why esProc SPL Beats SQLite for Lightweight Data Processing

Many micro‑applications require data processing and calculation capabilities, and while SQLite is a lightweight choice, it has several drawbacks that make it cumbersome for more complex scenarios.

Limitations of SQLite

SQLite provides weak and cumbersome support for external data files and other data sources, lacks stored procedures, and therefore relies heavily on the host program to manage workflow, leading to inefficient data exchanges and complex SQL that is hard to write and maintain.

esProc SPL as a Solution

esProc is a pure Java library that can be added to any Java application as a JAR, offering seamless integration and a standard JDBC interface. Instead of SQL, it uses its own query language called SPL.

Class.forName("com.esproc.jdbc.InternalDriver");
Connection conn = DriverManager.getConnection("jdbc:esproc:local://");
Statement statement = conn.createStatement();
ResultSet result = statement.executeQuery("=T(\"Orders.csv\").select(Amount>1000 && like(Client, \"*s*\"))");

For complex calculations, SPL is considerably simpler than SQL. For example, to find the top n customers whose sales account for half of total sales and sort them descendingly, the SQL version requires multiple CTEs, window functions, and nested queries, whereas SPL can express the same logic in a few straightforward steps.

with A as (select client, amount, row_number() over (order by amount) ranknumber from sales)
select client, amount from (
  select client, amount, sum(amount) over (order by ranknumber) acc from A
) where acc > (select sum(amount)/2 from sales) order by amount desc;

SPL provides richer collection operations and allows step‑by‑step calculations that follow natural thinking:

=sales.sort(amount:-1)               // sort by sales amount descending
=A1.cumulate(amount)                 // compute cumulative sequence
=A2.m(-1)/2                         // total amount (half of cumulative)
=A2.pselect(~>=A3)                  // select positions exceeding half
=A1(to(A4))                         // retrieve values by position

SPL code is written in cells rather than plain text, and the language includes full flow‑control statements such as for loops and if branches, effectively providing stored‑procedure capabilities directly within SPL.

Class.forName("com.esproc.jdbc.InternalDriver");
Connection conn = DriverManager.getConnection("jdbc:esproc:local://");
CallableStatement stmt = conn.prepareCall("call queryOrders()");
stmt.execute();

Scripts are interpreted, so changes take effect immediately without recompilation, and they can be stored outside the host program for hot‑swap updates.

Extensive Data Source Support

esProc can read and write a wide variety of data sources, including text files, Excel files, relational databases, NoSQL databases, HTTP, Kafka, JSON, and XML, using concise one‑ or two‑line commands.

T("Orders.csv").select(Amount>2000 && Amount<=3000)
Orders = json(httpfile("http://127.0.0.1:6868/api/orders").read())
db = connect("mysql")
db.query("select * from salesR where SellerID=?", 10)

Data can also be persisted directly from SPL, for example:

file("Orders.csv").export@t(A2)
file("Orders.xlsx").xlsexport@t(A2)
db.update(NewTable:OldTable)

SPL offers a proprietary binary file format for higher read/write performance.

Like SQLite, esProc is lightweight—the core JAR is about 15 MB and a full deployment is roughly 1 GB, allowing smooth operation on Android devices. However, it currently only has a Java implementation, making integration with non‑Java environments and iOS more challenging.

esProc is free and open source; it can be obtained from the GitHub repository:

https://github.com/SPLWare/esProc