Batch INSERT vs. Row-by-Row: The Performance Math Behind Bulk SQL Loading

Same Data, Same Server, 600x Difference

You have a spreadsheet with 50,000 rows that needs to land in a table. You generate INSERT statements and run them. Twenty minutes later it is still going, and someone asks whether the database is having problems.

The database is fine. The problem is that you sent 50,000 separate statements, each one paying a full round trip to the server. Regenerate the same data as batched multi-row INSERTs inside a single transaction and the load finishes in a few seconds. Nothing about the hardware changed. Nothing about the data changed. Only the shape of the statements changed.

This is one of the widest performance gaps in routine database work, and it is almost entirely mechanical. Understanding where the time actually goes makes the correct choice obvious rather than a matter of folklore.

Where the Time Actually Goes

To reason about this properly, separate the cost of a single-row INSERT into its components. The actual work of writing the row is usually the smallest part.

Network Round Trip

Every statement is a request and a response. On the same host this might be 0.1 ms. Across an availability zone, 0.5 to 2 ms. Across regions, 30 to 80 ms. Multiply by 50,000: at 1 ms you have spent 50 seconds doing nothing but waiting. At 50 ms you have spent 42 minutes.

This cost is serial and unavoidable for single statements, because the client must receive the response before sending the next one. It is the dominant term in most slow loads and it has nothing to do with database performance.

Statement Parsing and Planning

Each statement text must be parsed, validated against the catalog, and planned. For a trivial INSERT the plan is simple, but the parse still happens 50,000 times. Prepared statements eliminate the repeated parse; they do not eliminate the round trip.

Transaction Overhead

This is the term people miss. If autocommit is on, every INSERT is its own transaction, which means every INSERT triggers a durability guarantee. The database must ensure the write-ahead log record is durable before acknowledging, which historically meant an fsync. Even on fast SSDs this is expensive relative to the row write itself, and on network storage it is dramatically so.

Fifty thousand transactions means fifty thousand durability barriers. One transaction means one.

Index and Constraint Maintenance

Each row must be added to every index and checked against every constraint. This cost is genuinely proportional to row count and cannot be batched away, though it can be deferred. On a table with six indexes, index maintenance can exceed the cost of the heap write by several times.

The Strategies, Ranked

These are the practical options, roughly in order of increasing throughput. The right choice depends on how much control you have over the loading environment.

The jump from the first row to the third row of that table is the one worth internalising, because it requires no special permissions, no file staging, and no dialect-specific syntax. It is just a different way of writing the same statement.

What Multi-Row INSERT Looks Like

The transformation is straightforward. Instead of repeating the statement prefix for every row, state it once and supply many value tuples.

-- Slow: 4 statements, 4 round trips, 4 transactions
INSERT INTO orders (id, customer, total) VALUES (1, 'ACME', 240.00);
INSERT INTO orders (id, customer, total) VALUES (2, 'Globex', 118.50);
INSERT INTO orders (id, customer, total) VALUES (3, 'Initech', 902.25);
INSERT INTO orders (id, customer, total) VALUES (4, 'Umbrella', 55.00);

-- Fast: 1 statement, 1 round trip, 1 transaction
INSERT INTO orders (id, customer, total) VALUES
  (1, 'ACME', 240.00),
  (2, 'Globex', 118.50),
  (3, 'Initech', 902.25),
  (4, 'Umbrella', 55.00);

At four rows this is a rounding error. At 50,000 rows split into batches of 1,000, you have gone from 50,000 round trips to 50. That is the entire trick.

When generating scripts with the Develop Box Utilities Excel-to-SQL converter, enabling the batch insert option produces exactly this shape, and selecting the correct target database ensures identifiers are quoted the way your dialect expects rather than with MySQL backticks that PostgreSQL will reject.

Choosing a Batch Size

Bigger is not monotonically better. Throughput improves steeply up to a point and then degrades, and the reasons for the degradation are worth knowing because they produce confusing symptoms.

• Statement size limits. MySQL enforces max_allowed_packet, commonly 4 MB or 64 MB. Exceed it and the statement is rejected outright with an error that does not obviously point at batch size.
• Parameter count limits. If you are using parameterised statements, PostgreSQL's wire protocol caps parameters at 65,535. With 12 columns per row that is roughly 5,400 rows maximum, and exceeding it fails in the driver rather than the database.
• Memory and log growth. A very large transaction accumulates undo or WAL data that must be retained until commit. On MySQL with InnoDB this can push the redo log toward its limit; on PostgreSQL it delays cleanup and bloats the transaction log.
• Lock duration. A single transaction inserting 500,000 rows holds locks for its entire duration. Concurrent readers may be fine under MVCC, but concurrent writers to the same pages will queue, and a long transaction is a long window for a deadlock to form.
• Failure granularity. If one row in a batch of 100,000 violates a constraint, the whole batch rolls back. You now know the load failed but not which row caused it, and you must either bisect or reload with smaller batches to find out.

The practical answer for most workloads is 500 to 5,000 rows per statement, with multiple statements grouped into transactions of roughly 10,000 to 50,000 rows. Start at 1,000 rows per batch and measure; the optimum is workload-specific but the curve is flat enough that being within an order of magnitude captures nearly all the benefit.

The Failure Modes That Only Appear at Scale

A loading script that works perfectly on 100 test rows can fail in interesting ways on 500,000 real ones. These are the ones worth anticipating.

Partial Completion Without a Record

Your script has 500 batches. Batch 347 fails. Batches 1 through 346 committed. The table now contains 346,000 rows of a 500,000-row dataset, and rerunning the script from the start will either duplicate them or fail on primary key conflicts.

The fix is to make the load idempotent from the beginning: either truncate and reload as a unit, use upsert semantics, or track a batch cursor in a control table. Deciding this after the failure is much more painful than deciding it before.

Index Maintenance Dominating

On a table with several secondary indexes, index updates can consume most of the load time. Dropping non-essential indexes before the load and recreating them afterward is frequently faster overall, because bulk index construction is far more efficient than incremental maintenance. This is only safe when the table is not serving live reads that depend on those indexes.

Autoincrement Gaps

Failed batches consume autoincrement values that are not reclaimed. If anything downstream assumes contiguous IDs, it will break. Do not rely on ID contiguity, ever, but especially not after a bulk load.

Type Coercion Surprises

Data that looked numeric in a spreadsheet may contain a stray value that coerces differently. A leading zero on a postal code becomes an integer. A date that Excel stored as a serial number arrives as 44562. These do not error; they silently insert wrong values, which is considerably worse than failing.

Validating the generated statements before execution catches this class of problem. Reading a formatted, readable INSERT script is a genuine review step, which is one reason generated SQL should be formatted rather than emitted as one enormous line.

A Practical Loading Checklist

1. Inspect the source in a table editor first. Remove empty rows, deduplicate, and confirm column types before generating anything. Errors caught here cost nothing; errors caught after 300,000 inserts cost a rollback.
2. Generate batched, dialect-correct INSERTs. Match the target database explicitly rather than assuming the generator's default.
3. Read the first and last batch by eye. Check quoting, NULL handling, and that no column has shifted.
4. Load into a staging table, not the target. Staging tables have no indexes, no foreign keys, and no triggers, so they load fast and cannot corrupt anything.
5. Validate in SQL. Row count, null counts per column, min and max on dates and numerics, and a distinct count on any column expected to be unique.
6. Move staging to target in one server-side statement. An INSERT SELECT from staging avoids a second network transfer entirely and gives you a single atomic promotion.

This sequence turns a risky one-shot operation into a series of reversible steps, and the staging pattern in particular means a bad load never touches production data.

Frequently Asked Questions

Why is batching faster if the database does the same work either way?

Because the row write is the small part. Batching eliminates per-statement network round trips, repeated parsing, and per-statement transaction durability barriers. The index and constraint work does remain proportional to row count, which is why batching gives a large speedup but not an unlimited one.

Should I always use the native bulk loader instead?

Use it when you can. It is the fastest option by a wide margin. But it usually requires elevated privileges and the ability to place a file where the server can read it, which is often unavailable on managed database services or in restricted environments. Batched INSERT scripts work everywhere and get you most of the way there.

How do I find which row broke a large batch?

Reload the failing batch alone with a much smaller batch size, or temporarily as single-row inserts, to isolate it. Better: prevent it by validating and cleaning the source data before generating SQL, since most batch failures come from type or constraint issues that are visible in the source.

Conclusion: Shape the Statements, Not the Server

When a bulk load is slow, the instinct is to look at the database: check the instance size, review the indexes, examine the configuration. Usually the answer is upstream of all of that, in how the statements were generated.

Batch the rows. Wrap the batches in transactions. Load into staging and promote server-side. These three decisions typically deliver a larger improvement than any amount of tuning, and they cost nothing beyond generating the SQL in the right shape to begin with.