Batches in SQLite

When running demos and arbitrary examples, not much emphasis is required to be placed on the type of queries and practices one uses to manipulate data within databases. It's a whole different ball game when dealing with production apps. That's where the need to optimize your queries for best performance, use of resources, and in some cases latency, becomes of essence.

In this post, we'll examine this notion while looking at one practice that applies to SQLite.

#Batches

Batches or batch statements are a series of SQL statements sent to the database server at the same time. This is more true in the libSQL sense than raw SQLite since the latter involves no network connectivity, and hence you are not likely to see much of a difference performance-wise.

While using the libSQL SDKs batch transactions can be performed by using the .batch() method.

let mut stmts = vec![];

stmts.push("begin".to_string());

for i in 1..(q_size + 1) {
    let curr_stmt = format!("insert into todos values (\"do task no. {i}\")");
    stmts.push(curr_stmt.to_string());
}

stmts.push("end;".to_string());

let stmts = stmts.join(";");

let _result = conn.execute_batch(&stmts).await;

You'd want to use batch statements like the one above when there's a need to perform several queries to the database in a single go. In Turso for example, each query constitutes a network request, and with a batch, you get to save on latency and improve the overall performance of your application.

Factoring in my not-so-great internet connection, I was able to get an average of 5.3s latency when looping through 10 queries vs 1.7s when sending a batch containing 1000 queries while using the LibSQL Rust SDK on a Turso database. Visit the GitHub repo with the source code to the statement loop through and batch versions of the tests I used to perform your first-hand tests.

Batches in libSQL implement ACID properties in that success commits all changes, while any failure results in a rollback with no modifications made to the database.

Here's an example on the Recipe Book reference app where we're using batch statements to submit a bunch of recipes belonging to a recipe in one go.

#What do I do if I want to perform dependent transactions?

In the case you want to ensure the consistency of reads and writes in a series of database transactions, you have two options to choose from.

The first is interactive transactions, which will give you a series of read and write operations in a transaction's scope, and control of when to commit or roll back changes.

But, the catch with interactive transactions is that they are anti-pattern, in that they lock the database for writing until committed or rolled back which can impact performance on high-latency or busy databases.

This leads up to the second option which is, batches.

On top of what we've already discussed above, batches offer us transactional guarantees, i.e. we can perform queries transactionally carrying out progressive actions in response to previous responses. In the process, we get to maintain a pattern while avoiding the constraints faced when using interactive transactions.

Here is an example that lets you emulate a high-latency interactive transaction example. While the expensive transaction above is running, try running another regular transaction against the same database, to emulate another client.
You will end up getting a LibsqlError: SQLITE_BUSY: database is locked error on the second client.

Here is a high-latency dependent batch example that you use as a control.

#So, where should we use batches?

After the above observation, my recommendation is that batch statements should be used when sending a bunch of queries to the database in one go, usually in places where we would be tempted to use loop statements.

To ensure that we are using this practice efficiently, other optimization practices such as the use of indexes and any proper refactoring of our source code should be taken into account to ensure that our applications scale and perform efficiently.

To make use of batches in SQLite, you can create a free account with Turso, which will also let you lower your latency by placing your data closer to your users with our edge infrastructure or performing zero-latency queries by embedding replicas right inside your application infrastructure.