Batch Operations

The previous two lessons measured the read path: how many queries run and how much data each query carries. The write path has its own scale.

Suppose loan data is being imported from another system: two thousand records need to be written. This can be done one by one, inside a single transaction, or with a single statement. All three approaches produce the same rows. This lesson measures the difference between them and shows where it comes from.

Four Approaches

The measurement sets up the database from scratch for every approach, so none of them benefits from a cache warmed by the one before it.

// bulk-insert.mjs — the same 2000 rows written four ways, durations are measured
import { DatabaseSync } from "node:sqlite";
import { rmSync } from "node:fs";

const ROW_COUNT = 2000;
const data = Array.from({ length: ROW_COUNT }, (_, i) => [
  (i % 200) + 1, (i % 60) + 1, `2025-${String((i % 12) + 1).padStart(2, "0")}-01`,
]);

function freshDatabase() {
  rmSync("transfer.db", { force: true });
  const db = new DatabaseSync("transfer.db");
  db.exec(`CREATE TABLE loan (loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL,
           member_id INTEGER NOT NULL, pickup_date TEXT NOT NULL)`);
  return db;
}

function measure(label, write) {
  const db = freshDatabase();
  const t = performance.now();
  write(db);
  const duration = performance.now() - t;
  const n = db.prepare("SELECT count(*) AS n FROM loan").get().n;
  db.close();
  console.log(`${label.padEnd(49)} rows=${n}  duration=${duration.toFixed(0).padStart(6)} ms  ` +
    `per_row=${(duration * 1000 / n).toFixed(1).padStart(6)} us`);
  return duration;
}

const a = measure("each row its own transaction", (db) => {
  for (const s of data) {
    db.prepare("INSERT INTO loan (book_id, member_id, pickup_date) VALUES (?,?,?)").run(...s);
  }
});

const b = measure("single transaction, statement prepared per row", (db) => {
  db.exec("BEGIN");
  for (const s of data) {
    db.prepare("INSERT INTO loan (book_id, member_id, pickup_date) VALUES (?,?,?)").run(...s);
  }
  db.exec("COMMIT");
});

const c = measure("single transaction, statement reused", (db) => {
  db.exec("BEGIN");
  const insert = db.prepare("INSERT INTO loan (book_id, member_id, pickup_date) VALUES (?,?,?)");
  for (const s of data) insert.run(...s);
  db.exec("COMMIT");
});

const d = measure("single transaction, 500-row multi-row statement", (db) => {
  db.exec("BEGIN");
  for (let i = 0; i < data.length; i += 500) {
    const batch = data.slice(i, i + 500);
    const placeholders = batch.map(() => "(?,?,?)").join(",");
    db.prepare(`INSERT INTO loan (book_id, member_id, pickup_date) VALUES ${placeholders}`)
      .run(...batch.flat());
  }
  db.exec("COMMIT");
});

console.log(`\nratios (relative to the first approach): ${(a/b).toFixed(1)}x  ${(a/c).toFixed(1)}x  ${(a/d).toFixed(1)}x`);

node bulk-insert.mjs

each row its own transaction                      rows=2000  duration=   376 ms  per_row= 188.2 us
single transaction, statement prepared per row    rows=2000  duration=     3 ms  per_row=   1.5 us
single transaction, statement reused              rows=2000  duration=     1 ms  per_row=   0.4 us
single transaction, 500-row multi-row statement   rows=2000  duration=     1 ms  per_row=   0.7 us

ratios (relative to the first approach): 127.9x  470.4x  276.9x

Durations depend on the hardware and the file system; the ratios show the structure.

The biggest jump is between the first two rows. The only difference between them is the transaction boundary. In the first approach, every INSERT opens and closes its own transaction; closing requires durability each time, meaning the data is expected to be guaranteed on disk. Two thousand commits mean two thousand durability points. The second approach has a single commit.

In batch writing, the first decision is not the statement shape but the transaction boundary. The SQL Fundamentals course introduced batch insert as a multi-row INSERT statement; the measurement shows that the real gain is not there.

The difference between the second and third rows comes from statement preparation. Re-preparing the statement for every row means parsing the same text two thousand times. Setting up the prepared statement once and reusing it brings this down to a single pass.

The fourth row is slower than expected in a local measurement. A multi-row statement produces a differently sized SQL text for every batch, so every batch gets re-parsed; on a local database this is more expensive than reusing a prepared statement. The gain of a multi-row statement lies elsewhere: it lowers the statement count, and with it the number of round trips over the network. A local measurement does not show this gain.

Batch Size

In a multi-row statement, the only number left to choose is the batch size.

// batch-size.mjs — the effect of batch size in a multi-row statement
import { DatabaseSync } from "node:sqlite";
import { rmSync } from "node:fs";

const ROW_COUNT = 2000;
const data = Array.from({ length: ROW_COUNT }, (_, i) => [
  (i % 200) + 1, (i % 60) + 1, `2025-${String((i % 12) + 1).padStart(2, "0")}-01`,
]);

for (const batch of [1, 10, 50, 200, 1000]) {
  rmSync("transfer.db", { force: true });
  const db = new DatabaseSync("transfer.db");
  db.exec(`CREATE TABLE loan (loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL,
           member_id INTEGER NOT NULL, pickup_date TEXT NOT NULL)`);
  const t = performance.now();
  db.exec("BEGIN");
  let statement = 0, longest = 0;
  for (let i = 0; i < data.length; i += batch) {
    const slice = data.slice(i, i + batch);
    const sql = `INSERT INTO loan (book_id, member_id, pickup_date) VALUES ${
      slice.map(() => "(?,?,?)").join(",")}`;
    longest = Math.max(longest, sql.length);
    db.prepare(sql).run(...slice.flat());
    statement += 1;
  }
  db.exec("COMMIT");
  const duration = performance.now() - t;
  console.log(`batch=${String(batch).padStart(4)}  statement=${String(statement).padStart(4)}  ` +
    `longest_sql=${String(longest).padStart(6)} characters  duration=${duration.toFixed(1).padStart(6)} ms`);
  db.close();
}

node batch-size.mjs

batch=   1  statement=2000  longest_sql=    65 characters  duration=   4.5 ms
batch=  10  statement= 200  longest_sql=   137 characters  duration=   1.6 ms
batch=  50  statement=  40  longest_sql=   457 characters  duration=   1.2 ms
batch= 200  statement=  10  longest_sql=  1657 characters  duration=   1.1 ms
batch=1000  statement=   2  longest_sql=  8057 characters  duration=   0.8 ms

Most of the gain comes from the early steps: going from one to ten cuts the duration to roughly a third, while going from fifty to a thousand adds only a modest further gain. The query text, in contrast, grows linearly, approaching eight kilobytes at a batch of a thousand rows.

Three factors limit batch size. The number of bound variables has an upper limit set by the engine; the variable count, multiplied by the column count per row, cannot exceed that limit. A long query text raises the parsing cost and memory use. And a large batch means more work to roll back if something fails.

For that reason, the batch is chosen not as “as large as possible” but at the point where the gain flattens. In this measurement that point falls between fifty and two hundred; it falls somewhere else in a different schema, but the shape of the curve is the same.

Batch Update

In an update, a different value must be written to each row; this can be done with a single UPDATE statement, but how to do it calls for a choice.

// bulk-update.mjs — three ways to write a different value to each of 1000 rows
import { DatabaseSync } from "node:sqlite";
import { rmSync } from "node:fs";

const ROW_COUNT = 1000;
const updates = Array.from({ length: ROW_COUNT }, (_, i) => [
  i + 1, `2025-${String((i % 12) + 1).padStart(2, "0")}-15`,
]);

function setup() {
  rmSync("transfer.db", { force: true });
  const db = new DatabaseSync("transfer.db");
  db.exec(`CREATE TABLE loan (loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL,
           member_id INTEGER NOT NULL, pickup_date TEXT NOT NULL, return_date TEXT)`);
  db.exec("BEGIN");
  const insert = db.prepare("INSERT INTO loan VALUES (?,?,?,?,NULL)");
  for (let i = 1; i <= ROW_COUNT; i++) insert.run(i, (i % 200) + 1, (i % 60) + 1, "2025-01-01");
  db.exec("COMMIT");
  return db;
}

function measure(label, apply) {
  const db = setup();
  const counter = { statement: 0 };
  const t = performance.now();
  apply(db, counter);
  const duration = performance.now() - t;
  const n = db.prepare("SELECT count(*) AS n FROM loan WHERE return_date IS NOT NULL").get().n;
  const example = db.prepare("SELECT return_date FROM loan WHERE loan_id = 7").get().return_date;
  db.close();
  console.log(`${label.padEnd(30)} updated=${n}  example(7)=${example}  ` +
    `statement=${String(counter.statement).padStart(4)}  duration=${duration.toFixed(1).padStart(6)} ms`);
  return duration;
}

const a = measure("one-by-one update", (db, counter) => {
  db.exec("BEGIN");
  const update = db.prepare("UPDATE loan SET return_date = ? WHERE loan_id = ?");
  for (const [id, date] of updates) { update.run(date, id); counter.statement += 1; }
  db.exec("COMMIT");
});

const b = measure("update from a value list", (db, counter) => {
  db.exec("BEGIN");
  const placeholders = updates.map(() => "(?,?)").join(",");
  db.prepare(`UPDATE loan SET return_date = v.return_date
              FROM (SELECT column1 AS id, column2 AS return_date FROM (VALUES ${placeholders})) AS v
              WHERE loan.loan_id = v.id`).run(...updates.flat());
  counter.statement += 1;
  db.exec("COMMIT");
});

const c = measure("via a staging table", (db, counter) => {
  db.exec("BEGIN");
  db.exec("CREATE TEMP TABLE staging (id INTEGER PRIMARY KEY, return_date TEXT NOT NULL)");
  const insert = db.prepare("INSERT INTO staging VALUES (?,?)");
  for (const [id, date] of updates) { insert.run(id, date); counter.statement += 1; }
  db.exec(`UPDATE loan SET return_date = (SELECT return_date FROM staging WHERE id = loan.loan_id)
           WHERE loan_id IN (SELECT id FROM staging)`);
  counter.statement += 1;
  db.exec("DROP TABLE staging");
  db.exec("COMMIT");
});

console.log(`\nratios (relative to one-by-one update): ${(a/b).toFixed(2)}x  ${(a/c).toFixed(2)}x`);

node bulk-update.mjs

one-by-one update              updated=1000  example(7)=2025-07-15  statement=1000  duration=   0.9 ms
update from a value list       updated=1000  example(7)=2025-07-15  statement=   1  duration=   1.0 ms
via a staging table            updated=1000  example(7)=2025-07-15  statement=1001  duration=   1.0 ms

ratios (relative to one-by-one update): 0.91x  0.98x

The duration of all three approaches is the same in the local measurement. This is not a null result, it is a result: as long as they stay inside the same transaction boundary, statement count does not determine the cost on a local database. The distinction sits in the statement column: a thousand, one, and a thousand and one. If the database is beyond the network, this number is directly the round-trip count, and the gap then multiplies a thousandfold.

The third approach has the highest statement count, yet it has a value of its own. A staging table lets the data first be loaded into a separate relation, validated there, and only then applied to the target. The target relation is touched with a single statement; a bad row never reaches it. Loading the staging table is itself a batch-insert job in its own right, and it follows the rules from this lesson’s first measurement.

Summary

• The same two thousand rows were written four ways: each row its own transaction took 376 ms, a single transaction 3 ms, and reusing a prepared statement 1 ms.
• The biggest gain came from the transaction boundary; making one commit instead of two thousand cut the duration by more than a hundred and twenty-fold.
• The gain from batch size flattens early: going from 1 to 10 cut the duration to roughly a third, going from 50 to 1000 added only a modest further gain, while the query text grew linearly.
• In batch update, the local duration of all three approaches came out equal; the distinction is in the statement count (1000, 1, 1001), and over a network this number turns into round-trip count.
• A staging table makes it possible to validate the data before it is applied to the target, and it touches the target with a single statement.

Next Step

Everything measured up to this point was work the application did itself: how many queries ran, how many bytes were carried, how many statements were sent. A query can pass every one of these metrics and still be slow. A single query, in a single transaction, working with only the needed columns, can still take seconds — because the database decides for itself how to execute that query. The next lesson reads that decision from inside the application, logs the slow query, and shows why the plan changed.