Skip to content
Tutorials Center
My learning

Topics

Lesson 31 of 37 ยท Performance and Patterns

Finding N+1 Queries, Bulk Inserts and Streaming Results

Measure before you optimize: count queries in tests, detect N+1 early, insert thousands of rows in one statement, update in bulk, stream big results and paginate by key.

ORM code is slow in two predictable ways: it sends too many queries (the N+1 pattern) and it moves too much data one object at a time (loading a million rows into memory, or inserting them one by one). The fixes are mostly a handful of habits. Before them comes the most important rule of performance work: measure first. This lesson gives you the tools to measure and the techniques that win most of the time.

Measure: count the queries a piece of code sends

A query counter turns "this endpoint feels slow" into a number you can put in a test. Wrap the engine's before_cursor_execute event in a small context manager:

Python A query counter you can reuse

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

In a test you would write assert q["n"] <= 2. When someone adds a lazy relationship access in a loop the test fails the same day, not six months later in production. Libraries such as sqlalchemy-utils and nplusone do this too, but the ten lines above need nothing extra.

Better still, aggregate in SQL when you only need numbers. The query below does the work of the naive loop in a single statement and returns no objects at all:

Python
from sqlalchemy import func
total = session.scalar(select(func.sum(Order.total)))
per_customer = session.execute(
    select(Customer.name, func.count(Order.id), func.sum(Order.total))
    .join(Customer.orders)
    .group_by(Customer.id)
).all()

Bulk inserts

Adding objects one at a time is slow mostly because of the per-object bookkeeping in the unit of work. SQLAlchemy 2.0 is much faster at it than older versions (it batches INSERTs using "insertmanyvalues"), but when you have thousands of rows and don't need the objects afterwards, skip the objects: pass a list of dictionaries to an insert() statement.

Python insert() with a list of dictionaries

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

For 5,000 rows the database driver receives one batched call instead of 5,000 separate ones. Add .returning(Reading.id) to the statement when you need the new ids back (supported by SQLite 3.35+, PostgreSQL, MariaDB and recent SQL Server). If you do need ORM objects, session.add_all() is still the right tool; the unit of work batches the INSERTs for you.

Bulk updates

session.execute(update(Model), [{"id": 1, "value": 5}, {"id": 2, "value": 9}]) performs a bulk UPDATE by primary key: the dictionaries must contain the primary key and the columns to change. For "same change to many rows" use a where-based update() as in Sessions and the Unit of Work.

Streaming large results

By default a query's results are fetched into memory. For an export of a million rows that is a problem. yield_per(n) (or execution_options(yield_per=n)) streams objects in chunks of n so memory stays flat:

Python Stream with yield_per and partitions

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

yield_per cannot be combined with joinedload on a collection, because the join would multiply rows across chunk boundaries. Use selectinload (which works with it) or do not eager-load collections in streamed queries.

Pagination: OFFSET versus keyset

LIMIT 20 OFFSET 100000 makes the database walk past 100,000 rows to throw them away, so deep pages get slower and slower. Keyset pagination (also called the seek method) remembers the last key seen and asks for "the next 20 after it", which an index answers instantly:

Python
# page 1
rows = session.scalars(select(Post).order_by(Post.id).limit(20)).all()
last_id = rows[-1].id

# next page: no OFFSET at all
rows = session.scalars(
    select(Post).where(Post.id > last_id).order_by(Post.id).limit(20)
).all()

For sorting by a non-unique column such as date, include the primary key as a tie-breaker in both the ORDER BY and the WHERE (a tuple comparison: tuple_(Post.created, Post.id) > (last_created, last_id)).

Quick wins checklist

  1. Count queries in tests of important endpoints.
  2. Replace lazy loops with selectinload / joinedload, or with an aggregate query.
  3. Select only the columns you need (select(User.id, User.name)) when you will not modify the objects. Rows of plain tuples skip the ORM bookkeeping.
  4. Use insert() with lists for imports; keep add_all() for normal writes.
  5. Stream large exports; paginate by key.
  6. Add the right indexes (next lesson).

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is the best first step when an ORM-backed page is slow?

    Measuring shows whether the problem is too many queries, one slow query or too much data.
  2. 2. Why is session.execute(insert(Model), list_of_dicts) fast for imports?

    The driver receives all rows in a batched call and the ORM does no per-object tracking.
  3. 3. What does yield_per(200) do?

    It does not limit rows; it controls the fetch size so big results do not load into memory all at once.
  4. 4. Why is keyset pagination faster than OFFSET for deep pages?

    OFFSET must read and discard all earlier rows, but a key comparison lets the index seek to the starting point.

Summary

  • Measure queries with an event-based counter and assert on it in tests.
  • Fix N+1 with eager loading or by aggregating in SQL.
  • Use insert() with a list of dictionaries for bulk loads; add returning() for ids.
  • Stream big results with yield_per or partitions.
  • Prefer keyset pagination to large OFFSETs.

Next: Indexes, Query Plans and Connection Pooling