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`);

{/* run: skip */}

node bulk-insert.mjs

The output varies from machine to machine; here is an example run:

each row its own transaction                      rows=2000  duration=    40 ms  per_row=  20.1 us
single transaction, statement prepared per row    rows=2000  duration=     4 ms  per_row=   1.8 us
single transaction, statement reused              rows=2000  duration=     1 ms  per_row=   0.7 us
single transaction, 500-row multi-row statement   rows=2000  duration=     1 ms  per_row=   0.4 us

ratios (relative to the first approach): 11.2x  29.8x  49.9x

Durations depend on the hardware and the file system; the ratios show the structure. On another machine the first row took 376 ms and the ratios came out as 127.9, 470.4 and 276.9. Because the persistence cost depends on the file system, the duration of the first row and the size of the ratios changed between the two runs; what did not change is that the first row is far slower than the others. The order of the third and fourth rows, on the other hand, flipped from one run to the other.

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 order of the fourth row and the third row is not fixed: in one run the multi-row statement was slower, in the other it was faster. A local measurement does not separate the two methods reliably. A multi-row statement produces a differently sized SQL text for every batch, so every batch gets re-parsed, which takes something away from the gain of reusing a prepared statement. The real 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

{/* mask: (?<=batch= 1 statement=2000 longest_sql= 65 characters duration=)\s*[3-9][.][0-9](?= ms) /} {/ mask: (?<=batch= 10 statement= 200 longest_sql= 137 characters duration=)\s*[01][.][0-9](?= ms) /} {/ mask: (?<=batch= 50 statement= 40 longest_sql= 457 characters duration=)\s*[01][.][0-9](?= ms) /} {/ mask: (?<=batch= 200 statement= 10 longest_sql= 1657 characters duration=)\s*[01][.][0-9](?= ms) /} {/ mask: (?<=batch=1000 statement= 2 longest_sql= 8057 characters duration=)\s*[01][.][0-9](?= ms) */}

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 markedly (to roughly a third in the example run), 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 the example run 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

{/* mask: (?<=duration=)\s*[0-9.]+(?= ms) /} {/ mask: [01].[0-9]+(?=x) */}

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 of the same order 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 40 ms and 376 ms in the two measured runs, while a single transaction took a few milliseconds at most.
• The biggest gain came from the transaction boundary; making one commit instead of two thousand cut the duration by 11-fold to 128-fold in the two measured runs.
• 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.