Python SQLite inserts: measuring one transaction versus one commit per row

Problem. Inserting many rows into a local SQLite file can spend more time finishing transactions than binding values. The concrete question is how much changes when the same 1,024 rows move from one transaction per insert to one transaction for the batch.

Baseline. The baseline uses sqlite3.connect(..., isolation_level=None) and one execute() call per row. Python documents this setting as leaving the underlying SQLite library in autocommit mode, while SQLite commits an implicit transaction when its statement finishes.^[1][2]

Solution. The comparison keeps the SQL and row values fixed, then wraps the same execute() loop in one explicit BEGIN and COMMIT. A third version uses executemany() inside that same boundary.^[1]

Measured result. At n = 1,024 on this host, the medians were 2,138.751 ms for the baseline, 9.844 ms for the one-transaction loop and 5.293 ms for executemany(). The batch version was 404.09x faster in this run, and all three paths returned the same row checksum.

Reproduce the result

Complete code

from __future__ import annotations
import hashlib
import platform
import sqlite3
import statistics
import tempfile
import time
from pathlib import Path
SIZES = (64, 256, 1024)
REPETITIONS = 5
INSERT_SQL = "INSERT INTO events(event_id, label, value) VALUES (?, ?, ?)"
def make_rows(size: int) -> list[tuple[int, str, int]]:
    return [(index, f"event-{index:05d}", (index * 37) % 1000) for index in range(size)]
def signature(rows: list[tuple[int, str, int]]) -> tuple[int, str]:
    digest = hashlib.sha256()
    for event_id, label, value in rows:
        digest.update(f"{event_id}|{label}|{value}\n".encode("utf-8"))
    return len(rows), digest.hexdigest()
def prepare_database(directory: str) -> tuple[sqlite3.Connection, str, int]:
    path = Path(directory) / "events.sqlite3"
    connection = sqlite3.connect(path, isolation_level=None)
    journal_mode = str(connection.execute("PRAGMA journal_mode=DELETE").fetchone()[0])
    connection.execute("PRAGMA synchronous=FULL")
    synchronous = int(connection.execute("PRAGMA synchronous").fetchone()[0])
    connection.execute(
        "CREATE TABLE events(event_id INTEGER PRIMARY KEY, label TEXT NOT NULL, value INTEGER NOT NULL)"
    )
    return connection, journal_mode, synchronous
def autocommit_execute(connection: sqlite3.Connection, rows: list[tuple[int, str, int]]) -> None:
    for row in rows:
        connection.execute(INSERT_SQL, row)
def execute_one_transaction(connection: sqlite3.Connection, rows: list[tuple[int, str, int]]) -> None:
    connection.execute("BEGIN")
    try:
        for row in rows:
            connection.execute(INSERT_SQL, row)
        connection.commit()
    except Exception:
        connection.rollback()
        raise
def executemany_one_transaction(connection: sqlite3.Connection, rows: list[tuple[int, str, int]]) -> None:
    connection.execute("BEGIN")
    try:
        connection.executemany(INSERT_SQL, rows)
        connection.commit()
    except Exception:
        connection.rollback()
        raise
STRATEGIES = {
    "autocommit_execute": autocommit_execute,
    "execute_one_transaction": execute_one_transaction,
    "executemany_one_transaction": executemany_one_transaction,
}
def run_once(strategy, rows: list[tuple[int, str, int]]) -> tuple[float, tuple[int, str], str, int]:
    with tempfile.TemporaryDirectory(prefix="read0nly-sqlite-") as directory:
        connection, journal_mode, synchronous = prepare_database(directory)
        started = time.perf_counter_ns()
        strategy(connection, rows)
        elapsed_ms = (time.perf_counter_ns() - started) / 1_000_000
        stored_rows = list(
            connection.execute("SELECT event_id, label, value FROM events ORDER BY event_id")
        )
        result = signature(stored_rows)
        connection.close()
        return elapsed_ms, result, journal_mode, synchronous
def main() -> None:
    print(f"python={platform.python_version()}")
    print(f"sqlite={sqlite3.sqlite_version}")
    print(f"platform={platform.system()} {platform.release()} {platform.machine()}")
    print("database=temporary on-disk SQLite file")
    print("journal_mode=DELETE synchronous=FULL")
    print(f"sizes={','.join(map(str, SIZES))} warmup=1 repetitions={REPETITIONS} timer=perf_counter_ns")
    print("measurement=insert transaction only; schema setup and verification excluded")
    for size in SIZES:
        rows = make_rows(size)
        expected = signature(rows)
        measurements: dict[str, list[float]] = {}
        observed_settings: tuple[str, int] | None = None
        for name, strategy in STRATEGIES.items():
            warmup_time, warmup_result, journal_mode, synchronous = run_once(strategy, rows)
            if warmup_result != expected:
                raise AssertionError(f"warmup mismatch for {name}: {warmup_result} != {expected}")
            observed_settings = (journal_mode, synchronous)
            samples: list[float] = []
            for _ in range(REPETITIONS):
                elapsed_ms, result, journal_mode, synchronous = run_once(strategy, rows)
                if result != expected:
                    raise AssertionError(f"result mismatch for {name}: {result} != {expected}")
                if (journal_mode, synchronous) != observed_settings:
                    raise AssertionError("SQLite settings changed between trials")
                samples.append(elapsed_ms)
            measurements[name] = samples
        print(f"n={size} rows={expected[0]} checksum={expected[1]}")
        for name, samples in measurements.items():
            print(
                f"{name} samples_ms={[round(value, 3) for value in samples]} "
                f"median_ms={statistics.median(samples):.3f}"
            )
        autocommit_median = statistics.median(measurements["autocommit_execute"])
        execute_median = statistics.median(measurements["execute_one_transaction"])
        executemany_median = statistics.median(measurements["executemany_one_transaction"])
        print(
            f"speedup_autocommit_vs_execute={autocommit_median / execute_median:.2f}x "
            f"speedup_autocommit_vs_executemany={autocommit_median / executemany_median:.2f}x "
            f"execute_vs_executemany={execute_median / executemany_median:.2f}x"
        )
if __name__ == "__main__":
    main()

Command

$ python3 sqlite_transaction_benchmark.py

Captured output

$ python3 sqlite_transaction_benchmark.py
python=3.13.5
sqlite=3.53.4
platform=Linux 6.12.47+rpt-rpi-v8 aarch64
database=temporary on-disk SQLite file
journal_mode=DELETE synchronous=FULL
sizes=64,256,1024 warmup=1 repetitions=5 timer=perf_counter_ns
measurement=insert transaction only; schema setup and verification excluded
n=64 rows=64 checksum=fee4e87d675332a08b6d2742be7c6f9211a2c1e11317fbea66053ab3601ce6d6
autocommit_execute samples_ms=[94.532, 105.502, 98.11, 96.039, 94.36] median_ms=96.039
execute_one_transaction samples_ms=[2.318, 2.471, 1.697, 2.138, 2.044] median_ms=2.138
executemany_one_transaction samples_ms=[2.164, 1.504, 1.525, 2.003, 2.2] median_ms=2.003
speedup_autocommit_vs_execute=44.92x speedup_autocommit_vs_executemany=47.95x execute_vs_executemany=1.07x
n=256 rows=256 checksum=6731242a8af847b23ee90edbb5fa000737c12c8faeabc12c3e222cc062548f35
autocommit_execute samples_ms=[432.468, 426.695, 413.205, 407.752, 408.901] median_ms=413.205
execute_one_transaction samples_ms=[3.87, 3.422, 3.383, 3.41, 7.412] median_ms=3.422
executemany_one_transaction samples_ms=[3.913, 3.018, 5.819, 2.723, 3.012] median_ms=3.018
speedup_autocommit_vs_execute=120.75x speedup_autocommit_vs_executemany=136.92x execute_vs_executemany=1.13x
n=1024 rows=1024 checksum=e26d481ee3ddfaf254a0f8f0cc542ef921f8e9338784e21777011dc9602aeffc
autocommit_execute samples_ms=[2279.076, 1724.74, 1914.921, 2138.751, 2247.948] median_ms=2138.751
execute_one_transaction samples_ms=[10.303, 8.188, 9.844, 9.334, 10.806] median_ms=9.844
executemany_one_transaction samples_ms=[5.429, 5.355, 5.042, 5.293, 5.236] median_ms=5.293
speedup_autocommit_vs_execute=217.26x speedup_autocommit_vs_executemany=404.09x execute_vs_executemany=1.86x
exit=0

Environment and versions

The captured command used CPython 3.13.5, SQLite 3.53.4, Linux 6.12.47+rpt-rpi-v8 on aarch64 hardware, and the Python standard library only. Each trial created a fresh temporary on-disk database, set journal_mode=DELETE and synchronous=FULL, created the table before timing, and removed the file afterwards.

Methodology

The tested sizes were 64, 256 and 1,024 rows. Each strategy had one untimed warm-up and five timed trials for each size. The timer covered the insert transaction only. Connection setup, table creation, input construction and result verification were outside the timed interval.

The source reads the complete table back after each trial and compares its row count and SHA-256 checksum with the generated input. A mismatch raises an exception before the result is printed. The raw sample vectors remain in the captured output, alongside each median.

The timer is time.perf_counter_ns(). Python describes perf_counter() as a high-resolution clock for short durations and provides the nanosecond form to avoid float precision loss.^[3] The median is the middle value of the five samples, not a value selected after removing an inconvenient run.

isolation_level=None prevents Python’s legacy wrapper from opening transactions implicitly, so the baseline leaves each insert statement to SQLite’s autocommit mode.^[1] SQLite documents that database commands run within transactions and that an implicit transaction is committed when its last active statement finishes.^[2] The two alternatives issue BEGIN, perform all inserts, then issue one COMMIT.

Limits of this experiment

The run covers one Raspberry Pi host, one CPython build, one SQLite build, one table shape and one on-disk configuration. DELETE journalling and FULL synchronous mode are explicit here, so the numbers do not describe WAL mode, another durability setting, another filesystem or a memory-only database.

The benchmark excludes connection and schema setup, verification queries, indexes beyond the primary key, triggers, concurrent readers and writers, and failures halfway through a batch. It also measures three complete batches, not the best chunk size for a long import. The timings are useful for this source and configuration; they are not a portable conversion factor.

The problem

The baseline performs one parameterised INSERT for every row and leaves transaction completion to SQLite after each statement. With 1,024 rows, that creates 1,024 transaction boundaries in the measured section. The row values and SQL text stay small, while the storage engine repeatedly closes the write unit.

That is a different workload from sending 1,024 statements inside one explicit transaction. The table result can be identical even when the commit schedule is completely different.

Baseline complexity

For n rows, autocommit_execute() makes n calls to Connection.execute() and allows n implicit transactions to finish. The count of transaction boundaries therefore grows linearly with the input. The time of each boundary depends on the journal and synchronous settings, storage latency and other activity on the host, so the code gives a precise operation count without giving a universal time per row.

The baseline is a reasonable control when each row must become durable or visible at its own boundary. It is a poor control for a bulk load whose intended unit of work is the whole batch.

The solution

execute_one_transaction() starts one transaction, runs the same Python loop and commits once. The only changed boundary is transaction scope.

executemany_one_transaction() keeps that boundary and hands the parameter sequence to executemany(). This removes the explicit Python loop from the function body while retaining the same SQL and values. The result check makes the comparison a correctness test as well as a timing test.

What the measurement shows

At 64 rows, the median fell from 96.039 ms with autocommit to 2.003 ms with executemany() inside one transaction, a 47.95x ratio. At 256 rows, the corresponding medians were 413.205 ms and 3.018 ms, a 136.92x ratio. At 1,024 rows, they were 2,138.751 ms and 5.293 ms, a 404.09x ratio.

The execute loop inside one transaction reached 9.844 ms at 1,024 rows. The executemany() version reached 5.293 ms, so the extra gap between those two implementations was much smaller than the gap created by transaction scope. The exact difference is still conditional on this Python wrapper, SQL statement, table and host.

All three strategies produced the same count and checksum at every size. That check covers the complete returned row sequence. A matching elapsed time without that check would leave room for a fast function that silently inserted the wrong data.

Practical applications

Bulk imports

A file or API import can accumulate a bounded batch, call executemany() inside one transaction and commit after the batch passes its checks. The benchmark measures the insert phase only. Parsing, validation, network reads and the right batch size still need their own measurements.

Event and log ingestion

An event collector can commit every fixed number of rows or every short time window instead of committing every event. The same mechanism reduces transaction boundaries, while the chosen window controls how long new events remain uncommitted and how much work is repeated after a failure. This article does not measure those recovery choices.

Test fixtures and build metadata

A test suite or build tool can load a known set of rows inside one transaction, then commit or roll back as a unit. That fits data whose useful boundary is the fixture set. A test that deliberately exercises per-row durability needs the baseline semantics instead, so the measured speedup is not a reason to change that contract.

These are applications of the measured transaction pattern, not benchmark results from a service, collector or test suite. Their storage mode, row shape and failure policy can change the result.

Where it stops helping

A single transaction becomes the wrong boundary when other connections need to observe each row, when batches cannot be rebuilt safely, or when the application requires independent durability decisions. SQLite allows multiple readers but only one simultaneous writer, and a long write transaction can therefore affect other work.^[2]

A large import also keeps all its failure handling at the batch boundary. Smaller transactions can reduce the amount of work repeated after a failure, at the cost of more commits. WAL mode, indexes, triggers, larger records and concurrent connections should be tested separately rather than folded into this result.

Sources

[1] sqlite3: DB-API 2.0 interface for SQLite databases | Python 3.13 documentation

[2] Transaction | SQLite documentation

[3] time: Time access and conversions | Python 3.13 documentation