A bulk import that switched on batching is barely faster. How do you determine whether batching is actually happening?
answer
- the statement count will not move
- round trips are the quantity
- make latency the variable
- batches over statements is the ratio
- batching on, still slow, look elsewhere
basics
~20 sMeasure round trips, not statements: a batch executes the same statements as before. Read the layer's or driver's batch counters, watch how runtime scales when link latency is added, then look for the things that silently split a batch.
solid answer
~40 sFirst discard the instrument that cannot answer. A batch executes the same statements, so a statement count reads the same whether the writes travelled in one trip or ten thousand; round trips and wall time are what move. Three sources of evidence work: the layer's or driver's counters for batch executions against statement executions, whose ratio is the answer directly; a run with extra link latency, where a per-row path scales with latency times rows and a batched path barely moves; and session-level counters for requests received on the database side. If batching is off, look for an immediately needed generated key, a read inside the write loop, mixed statement shapes, or a per-row check. If it is on and nothing improved, round trips were never the bottleneck.
go deeper
Remember that turning batching on does not change how many statements run, so a statement count cannot confirm it.
Explain which quantities move -- round trips, statements per batch, wall time -- and name two causes that silently disable batching.
Design the measurement: batch counters or a latency-scaling run, then a ranked list of causes, and know when to conclude that round trips were never the bottleneck.
Decide what is worth instrumenting permanently for write paths, and set the batch size against memory, lock duration, failure granularity and restart points rather than peak throughput alone.
## Why the obvious measurement is blind here The reflex is to count statements. That reflex is right for the read side, where a fan of per-row queries shows up as a count that tracks row volume, and it is useless here. **Batching does not change which statements execute** -- the same insert runs once per row inside the engine either way. It changes how many times the application stops and waits. A count that is identical before and after tells you nothing, and a team that treats it as proof will conclude batching is off when it is on, or the reverse. The quantities that actually move: - **Round trips**, or equivalently requests received per session. - **Wall time**, but only the part of it that was waiting. - **Statements per batch execution**, if the layer reports it. ## Three ways to get the evidence **1. Ask the layer or the driver.** Most write paths expose counters for batch executions alongside statement executions. Batches over statements gives the effective batch size directly: a ratio near one means every statement travelled alone, whatever the configuration claims. **2. Make latency the independent variable.** This is the cheapest decisive test and needs no instrumentation at all. Run the same import twice with an added delay on the link, or compare a co-located database against a remote one, and plot runtime. | Behaviour under added latency | What it means | |---|---| | Runtime grows roughly by latency times row count | One trip per row; batching is not in effect | | Runtime grows by latency times batch count | Batching is working; the slope reveals the batch size | | Runtime barely responds to latency at all | The bottleneck is elsewhere -- engine work, or the data producer | **3. Look from the database side.** Session-level counters for requests received, compared against rows written, give the same ratio without trusting the application's own view. This also catches the case where the layer batches but something below it unpacks the group. ## When the counters say batching is off Work through the conditions that quietly disable it, in the order they are most often the cause: 1. **A generated key needed immediately.** If the identifier is produced by the engine as the row is inserted and the layer must have it at once, every insert executes alone. Keys assigned before the insert -- client-side, or drawn from a pre-allocated block -- restore accumulation. 2. **A read inside the write loop.** A lookup that must reflect pending changes forces the layer to send what it is holding. Every such read caps the batch at the work done since the last one. Resolve lookups up front. 3. **Mixed statement shapes.** Interleaved writes to two tables produce alternating texts. Either the layer groups by shape or nothing accumulates. 4. **A per-row check.** A concurrency check that compares an expected outcome for one row may prevent batching -- layers differ in whether they can read per-statement outcomes back from a group, so confirm rather than assume. 5. **A commit or flush per iteration.** The most banal cause: the loop's own transaction boundary. Nothing can accumulate across a boundary that closes every turn. ## When the counters say batching is on and nothing improved Then the diagnosis was wrong, and this is the more valuable half of the answer. Batching only removes waiting. If the import was bound by something else, removing the waiting changes little: - **Per-row engine work.** Index maintenance on several secondary indexes, foreign key validation and constraint checking are paid per row regardless of how the row arrived. - **Write amplification from the code above.** If each logical change rewrites a whole collection, the real row count is a multiple of what you assumed. - **The producer.** Parsing the source, network fetches, or a transformation step can be the actual ceiling; the database was never busy. - **Contention.** Long transactions waiting on locks held elsewhere do not care how the statements arrived. ## Choosing the batch size once it works Start in the hundreds to low thousands of rows and measure; the curve flattens quickly once round trips stop dominating. Larger batches buy progressively less while costing more: accumulated memory, a longer-held set of locks, a bigger transaction to undo, coarser failure granularity when one row is rejected, and fewer restart points for a long import. Pick the smallest size that reaches the throughput you need, and clear the layer's accumulated state on the same rhythm so memory stays flat across the run.
- How do you choose a batch size once you have confirmed batching works?Start in the hundreds to low thousands and measure; the gain flattens once round trips stop dominating. Bigger batches cost accumulated memory, longer-held locks, a larger transaction to undo, coarser failure granularity and fewer restart points. Take the smallest size that meets the throughput target, and clear accumulated state on the same rhythm.
- Runtime is unchanged although the batch counters prove batching is on. What now?Round trips were not the bottleneck. Look at per-row engine work such as index maintenance and constraint validation, at write amplification from the code above -- a collection rewritten wholesale multiplies the true row count -- at the producer feeding the import, and at lock contention. Batching removes waiting only.
- Why is added link latency a fairer test than simply timing the import once?Because it turns a single number into a slope. One timing conflates waiting with engine work and producer cost. Deliberately increasing the per-trip cost separates them: a per-row run's runtime rises with latency times rows, a batched run with latency times batches, and a run bound by anything else hardly responds.
saying these in an interview costs you the question
- Uses a statement count to prove batching is working
- Assumes the configuration flag is proof that batches are sent
- Never checks whether round trips were the bottleneck at all
- Keeps raising the batch size when the curve has already flattened
- Forgets that a read inside the write loop forces an early send