Chatty Data Access

Chatty data access is performing one logical operation through many small, separate database calls instead of a few coarse-grained ones. Inserting 1,000 rows with 1,000 individual INSERT statements, or updating records one at a time inside a loop, are textbook examples. It is closely related to the N+1 problem but applies to writes and procedural processing as much as to reads.

Why It Happens

Procedural code naturally expresses work row by row: iterate a collection and do one database operation per item. ORMs make single-row saves trivial and batch operations less obvious. Developers think in terms of objects rather than sets, so set-based SQL does not come to mind. The pattern works fine for small inputs and only hurts at volume.

Why It Hurts

Every database call pays fixed overhead — network round-trip, statement parsing, planning, and per-statement transaction bookkeeping. When that overhead is multiplied by thousands of tiny calls, it dwarfs the actual data work, and total latency is dominated by round-trips rather than by the database doing anything useful. Throughput collapses and the connection pool saturates because each call holds a connection briefly but the calls never stop. Many tiny transactions also increase commit overhead and lock churn. The same data, moved in a few batched calls, can be orders of magnitude faster.

Warning Signs

• A loop issues one INSERT, UPDATE, or DELETE per iteration.
• Bulk imports or updates process records one at a time.
• A single logical operation generates dozens or hundreds of round-trips.
• Throughput is far below the database's raw capacity and dominated by latency.

Better Alternatives

Think in sets. Use bulk insert and multi-row update statements to move many rows per round-trip. Push logic into set-based SQL so the database operates on whole sets at once instead of receiving them piecemeal. Batch related reads and writes into coarse-grained calls, and group work into appropriately sized transactions rather than one per row. ORMs typically offer bulk APIs; use them on hot paths.

How to Refactor Out of It

Find the loops that issue per-row database calls, usually visible as bursts of identical statements in query logs. Replace them with batched operations: a single multi-row INSERT, an UPDATE ... FROM over a set, or a bulk API call. Choose a sensible batch size to bound memory and lock duration. Wrap each batch in one transaction rather than many. Measure the reduction in round-trips and the throughput gain. Adopt set-based thinking as the default for any operation touching more than a handful of rows.