Tech Tip #20: Write in bulk, not row by row

Inserting 10,000 rows? Don't send 10,000 statements.

Video created by explainapaper.com

The problem

Loops that insert, update or delete one row per iteration pay a full round trip and a separate statement every time. It is the write-side twin of the N+1 query and just as easy to miss in review.

The fix you can use today

  • Use the bulk form your driver offers: executemany(), bulk_create(), COPY or a multi-row INSERT.
  • Batch updates with one statement and a WHERE ... IN (...) where the logic allows.
  • Wrap a large batch in one transaction instead of committing per row.

GreenCode researchers list one query per item among the worst energy wasters in code.

Tools that help

  • GreenCode Constitution skill (GreenCode researchers): open-source energy principles and a catalogue of 37 energy anti-patterns that an AI coding agent can follow to find and fix waste like this
  • Django Debug Toolbar: lists every SQL query a request made, so repeated queries in a loop stand out

How GreenCode helps

GreenCode helps tackle this by checking a codebase for quality, security and green software patterns in one pass. Row-by-row writes come out as ranked defects, alongside the bugs and security findings. More on this.

Tags

#sql#orm#databases#performance

Sources

Transcript

Inserting ten thousand rows? Do not send ten thousand statements. Many loops write one row to the database each time round. Each row pays for a full round trip, and its own statement. It is the write-side twin of the N plus one query. And it is just as easy to miss in review. Here is the fix you can use today. Use the bulk form your database library offers. Like executemany, bulk create, COPY, or one insert with many rows. Batch your updates into one statement where the logic allows. Wrap a large batch in one transaction, not one commit per row. Fewer statements mean less work for the database, for the same result. GreenCode researchers list one query per item among the worst energy wasters in code. GreenCode's Constitution skill can help an AI coding agent find and fix these loops. To see them yourself, use tools like Django Debug Toolbar, which lists every query. GreenCode helps tackle this by checking code for quality, security and energy waste together. Row-by-row writes come out as ranked defects, beside the bugs. That was GreenCode Tech Tip number twenty. Follow for the next tip, and find more on greencode.ai/tech-tips.

All GreenCode Tech Tips · All GreenCode videos

  • Tip #20
  • For Developers
  • Length 1:15
  • Category Databases, APIs and storage
GreenCode

Want this checked on your own system? Talk to the GreenCode team.

Get in touch