# Find and fix N+1 queries with your tests

> N+1 queries run one query per row. Fix them with select_related/prefetch_related or SQLAlchemy's selectinload/joinedload, and lock the fix in with assertNumQueries.

An N+1 query is a loop that touches a relation the ORM hasn't loaded yet: one query fetches 50 orders, then each `order.customer` fetches its customer, 51 queries in all. Each query is quick, which is why nobody notices, but the round trips add up: in a test, in a view under load, in a nightly job over 100,000 rows.

This is the one speed problem in this series where the fix goes in application code, not in the tests. Fixing it makes the tests faster and production faster.

## Why it happens

ORMs load relations lazily by default. Django runs a query the first time you access a foreign key or a reverse relation on an instance that doesn't have it cached; SQLAlchemy does the same for relationships with the default `lazy="select"` loading. Code that's fine for one object becomes N queries inside a loop, a template `{% for %}`, or a serializer that renders a list.

```python
# One query for the books, then one per book for its author
def titles():
    return [f"{book.title} by {book.author.name}" for book in Book.objects.all()]
```

In a test with three books that's four queries. The test is a good place to see it because you control exactly how many rows there are.

## The fix in Django: select_related and prefetch_related

Use `select_related()` for foreign keys and one-to-one fields: Django joins the related table into the same query.

```python
def titles():
    return [f"{book.title} by {book.author.name}" for book in Book.objects.select_related("author")]
```

Use `prefetch_related()` for reverse foreign keys and many-to-many relations: Django runs one extra query for all of them and attaches the results in Python.

```python
def books_by_author():
    authors = Author.objects.prefetch_related("books")
    return {author.name: [book.title for book in author.books.all()] for author in authors}
```

That's two queries however many authors there are. To filter or order the prefetched rows, pass a `Prefetch` object, e.g. `Prefetch("books", queryset=Book.objects.order_by("title"))`. Filtering `author.books.filter(...)` inside the loop runs a new query per author and undoes the prefetch.

Other common sources: counting related rows in a loop (use `annotate(Count("books"))`), and calling `.get()` or `.exists()` per item (fetch everything once into a dict).

## Lock it in with assertNumQueries

Once a view or function runs a fixed number of queries, assert it, so the next change that brings the N+1 back fails a test:

```python
from django.test import TestCase
from model_bakery import baker

from shop.models import Author, Book
from shop.reports import titles


class TitlesTests(TestCase):
    @classmethod
    def setUpTestData(cls):
        author = baker.make(Author)
        baker.make(Book, author=author, _quantity=10)

    def test_titles_runs_one_query(self):
        with self.assertNumQueries(1):
            result = titles()
        self.assertEqual(len(result), 10)
```

Create more than one or two related rows, or an N+1 looks like a constant. With pytest-django, the fixtures do the same:

```python
import pytest
from model_bakery import baker

from shop.models import Book
from shop.reports import titles


@pytest.mark.django_db
def test_titles_runs_one_query(django_assert_num_queries):
    baker.make(Book, _quantity=10)
    with django_assert_num_queries(1):
        titles()
```

`django_assert_max_num_queries` asserts an upper bound instead, which is less brittle for views whose query count legitimately changes (sessions, permission checks). When the count is wrong, `assertNumQueries` lists the queries that ran, and the pytest-django fixtures do too when you run pytest with `-v`, so the repeated one is easy to spot.

## The fix in SQLAlchemy: selectinload and joinedload

SQLAlchemy 2.0 sets loading strategies per query with `options()`:

```python
from sqlalchemy import select
from sqlalchemy.orm import joinedload, selectinload

# Collections (one-to-many, many-to-many): one extra SELECT ... WHERE author_id IN (...)
authors = session.scalars(select(Author).options(selectinload(Author.books))).all()

# Many-to-one: joined into the same query
books = session.scalars(select(Book).options(joinedload(Book.author))).all()
```

`selectinload` is the usual choice for collections; `joinedload` suits many-to-one relations. You can also set a default on the relationship, `relationship(lazy="selectin")`, or make accidental lazy loads fail with `raiseload("*")` in tests that should load everything up front. When the loop only needs an aggregate, replace per-row queries with one grouped query (a join with `GROUP BY`).

SQLAlchemy has no `assertNumQueries`, but an engine event gives you one:

```python
from contextlib import contextmanager

from sqlalchemy import event, select
from sqlalchemy.orm import selectinload

from library.models import Author


@contextmanager
def count_queries(engine):
    statements = []

    def before_cursor_execute(conn, cursor, statement, parameters, context, executemany):
        statements.append(statement)

    event.listen(engine, "before_cursor_execute", before_cursor_execute)
    try:
        yield statements
    finally:
        event.remove(engine, "before_cursor_execute", before_cursor_execute)


def test_authors_with_books_runs_two_queries(engine, session):
    with count_queries(engine) as statements:
        authors = session.scalars(select(Author).options(selectinload(Author.books))).all()
        assert all(author.books for author in authors)
    assert len(statements) == 2
```

## Keep the tests correct

This changes application code, so it speeds up production too; the tests stay as they are. Two things to check:

- Eager loading the wrong thing costs more than it saves: `select_related` on a large many-to-one joins wide rows, and a prefetch nobody reads is a wasted query. Load what the code actually uses.
- Query-count assertions should run against realistic data: several rows and the relations the code touches, so the count proves the query doesn't grow with N.

## How flakytest finds it

flakytest counts every test's queries through Django's `execute_wrapper` and SQLAlchemy's engine events, normalizes the SQL (numbers, strings and `IN (...)` lists become placeholders) and shows the statements a test repeats in the report's Database section. When a test runs the same `SELECT` 10 or more times, it suggests **Repeated queries (N+1) in the code under test**, names the tests with the most repeats and shows the repeated SQL, e.g. `25x in test_order_list: SELECT ... FROM "shop_customer" WHERE "shop_customer"."id" = ?`. The estimated saving scales each of those tests' query time down as if its most repeated statement ran once.

## Further reading

- [Django: database access optimization](https://docs.djangoproject.com/en/stable/topics/db/optimization/)
- [Django: `select_related()`](https://docs.djangoproject.com/en/stable/ref/models/querysets/#select-related) and [`prefetch_related()`](https://docs.djangoproject.com/en/stable/ref/models/querysets/#prefetch-related)
- [Django: `assertNumQueries()`](https://docs.djangoproject.com/en/stable/topics/testing/tools/#django.test.TransactionTestCase.assertNumQueries)
- [pytest-django: `django_assert_num_queries`](https://pytest-django.readthedocs.io/en/latest/helpers.html#django-assert-num-queries)
- [SQLAlchemy: relationship loading techniques](https://docs.sqlalchemy.org/en/20/orm/queryguide/relationships.html)