SQL BenchMEASURED, NOT GUESSED

    ↑↓ to select, Enter to open, Esc to close

    one transaction (500 rows) is ~250.00× faster than commit per row

    The biggest gap on this site: 500 rows in one transaction is 250× faster than committing per row. The difference is 500 disk syncs vs one.

    Result

    Runone transaction (500 rows)commit per rowRatio
    Run 18.0382221.9830.00×
    Run 27.6072057.9930.00×
    Run 38.4252039.4640.00×

    Spread over 3 runs: one transaction (500 rows) CV 4.2% / commit per row CV 4.0% / Ratio CV 0.0%

    one transaction (500 rows): 8.04mscommit per row: 2221.98msRun1one transaction (500 rows): 7.61mscommit per row: 2057.99msRun2one transaction (500 rows): 8.43mscommit per row: 2039.46msRun3

    The statement

    con.execute('BEGIN')
con.executemany('INSERT INTO items VALUES (?,?,?,?,?)', rows)
con.commit()
    for r in rows:
    con.execute('INSERT INTO items VALUES (?,?,?,?,?)', r)
    con.commit()

    Conditions

    500 rows inserted into an 8,000-row table; `journal_mode=DELETE`, `synchronous=NORMAL`.

    Where this number does not apply

    The magnitude changes on non-SSD storage and with `synchronous=OFF`; fewer rows means a smaller gap.

    Reproduce

    cd bench && python sqbench.py --samples 9 --out ../data/run.json