A Django dashboard view runs the same SELECT four times; how does the QuerySet result cache explain it, and what fixes it?

middlemust knowfreq 60%

answer

the cache lives on one object
new QuerySet, empty cache
helpers and .all() clone
evaluate once, reuse the variable

basics

~20 s

Each QuerySet caches its own rows after its first full evaluation, but helpers, .all(), filter() and template lookups like user.orders.all build new QuerySets with empty caches. Evaluate one QuerySet once and reuse that object everywhere.

solid answer

~40 s

The result cache belongs to a single QuerySet object: its first full evaluation fills it, and later iteration, len(), bool() or count() on that same object read from it. The dashboard repeats the SELECT because it keeps creating new QuerySets: a helper that returns Order.objects.filter(...) on every call, an .all() or .filter() on an already evaluated QuerySet, or a template that writes user.orders.all in an if, a for and a length. Each new object starts with an empty cache. The fix is to build the QuerySet once, store it in a variable, evaluate it once (a loop, list(), or if orders:), and pass that same object to everything that needs the rows, doing further narrowing in Python. When only a number is needed, count() or an aggregate is cheaper than loading rows.

The result cache belongs to a single `QuerySet` object: its first full evaluation fills it, and later iteration, `len()`, `bool()` or `count()` on that same object read from it. The dashboard repeats the SELECT because it keeps creating new QuerySets: a helper that returns `Order.objects.filter(...)` on every call, an `.all()` or `.filter()` on an already evaluated QuerySet, or a template that writes `user.orders.all` in an `if`, a `for` and a `length`. Each new object starts with an empty cache. The fix is to build the QuerySet once, store it in a variable, evaluate it once (a loop, `list()`, or `if orders:`), and pass that same object to everything that needs the rows, doing further narrowing in Python. When only a number is needed, `count()` or an aggregate is cheaper than loading rows.

code

python · 18 lines

python

from django.shortcuts import render

from orders.models import Order


def open_orders():
    return Order.objects.filter(status='open').order_by('-created_at')


def dashboard(request):
    if not open_orders():                                 # 1: full SELECT, object discarded
        return render(request, 'orders/empty.html')
    context = {
        'count': len(open_orders()),                      # 2: same SELECT again
        'orders': open_orders(),                          # 3: same SELECT in the template loop
        'total': sum(o.total for o in open_orders()),     # 4: same SELECT again
    }
    return render(request, 'orders/dashboard.html', context)

go deeper

for a junior

Remember that a QuerySet keeps its rows only on that one object, so store it in a variable and reuse it.

for a middle

Explain which calls create new QuerySets with empty caches (helpers, all, filter, template chains) and why indexing an unevaluated QuerySet queries each time.

for a senior

Diagnose repeated identical SQL with assertNumQueries or connection.queries, then decide between reusing one evaluated QuerySet and issuing smaller count or aggregate queries.

for a principal

Set team conventions for where QuerySets are evaluated, such as views and not templates, so repeated-query regressions are caught by review and query-count tests.

Where the result cache lives

Every Django QuerySet has a private result cache. It starts empty. The first time the QuerySet is fully evaluated, by iteration, list(), len(), bool() or in, Django runs the SELECT, stores the model instances, and serves every later full read of that object from memory. count(), exists() and contains() also check it before querying.

The cache is per object, not per SQL string. Two QuerySets that would produce identical SQL do not share anything. Django has no cross-QuerySet query cache and no request-wide cache of rows.

How a dashboard ends up with four identical queries

The usual causes, in the order they show up in code review:

A helper that builds a fresh QuerySet on every call. open_orders() returns Order.objects.filter(status='open'); calling it four times makes four objects and four SELECTs.
Cloning an evaluated QuerySet. orders.all() and orders.filter(...) return new QuerySets; they do not inherit the parent's cache, so they query again even when the parent already holds the rows.
Template attribute chains. {% if user.orders.all %}, {% for o in user.orders.all %} and {{ user.orders.all|length }} each call the related manager's all() and get a new QuerySet, so each line queries.
Indexing an unevaluated QuerySet. orders[0] and orders[0] again run two LIMIT 1 queries, because index and slice access do not fill the cache.

The fix

Build the QuerySet once and keep it in a variable.
Evaluate it once, deliberately: iterate it, call list(), or test if orders: when you will loop over the rows anyway.
Pass the same object into the template context and helpers. The template's {% if %}, {% for %} and |length then all read the filled cache.
Narrow already-loaded rows in Python ([o for o in orders if o.total > 100]) instead of calling filter(), which would query again.
In templates, use {% with orders=user.orders.all %} to bind one QuerySet for the block, or pass it from the view.

When not to load the rows at all

Caching only helps when you need the rows. If the dashboard shows a number, Order.objects.filter(status='open').count() sends one COUNT(*) instead of transferring every row; if it shows a total, an aggregate computes it in the database. Mixing strategies carelessly also doubles work: if orders.exists(): followed by a loop over orders runs two queries, while if orders: followed by the loop runs one.

Code	Queries
`open_orders()` called four times	4
`orders = open_orders()`; `if orders:`, loop, `len(orders)`, `sum(...)`	1
`if orders.exists():` then loop	2
`orders.filter(total__gt=100)` after evaluation	+1
`orders.count()` after evaluation	+0

The template-side version of the bug

The view may be innocent and the template may make the copies:

{% if request.user.orders.all %} builds QuerySet one and evaluates it.
<h2>{{ request.user.orders.all|length }} open</h2> builds QuerySet two.
{% for o in request.user.orders.all %} builds QuerySet three.
{{ request.user.orders.all.0 }} builds QuerySet four and runs a LIMIT 1.

Each attribute chain is resolved afresh, so each line gets its own object. Two fixes work: evaluate the orders in the view and pass one object as orders, or wrap the block in {% with orders=request.user.orders.all %} so every tag inside refers to the same QuerySet. After that change, the if, length, for and .0 all read the cache filled by the first full evaluation.

Confirming it

Measure before and after. In a test, self.assertNumQueries(1) from Django's TestCase around the view call fails loudly if someone reintroduces a clone. In development, django.db.connection.queries lists the statements run when DEBUG is on. Look for identical SQL strings appearing several times; the result cache rule tells you that some line built a new QuerySet for each of them.

Caveats

A long-lived cache is stale by design: the rows reflect the moment of evaluation. Inside one request that is what you want; across requests it is a bug.
Loading every row to call len() on a large table is worse than a COUNT(*); reuse the cache only when the rows are needed anyway.

## Where the result cache lives Every Django `QuerySet` has a private **result cache**. It starts empty. The first time the QuerySet is fully evaluated, by iteration, `list()`, `len()`, `bool()` or `in`, Django runs the SELECT, stores the model instances, and serves every later full read of **that object** from memory. `count()`, `exists()` and `contains()` also check it before querying. The cache is **per object**, not per SQL string. Two QuerySets that would produce identical SQL do not share anything. Django has no cross-QuerySet query cache and no request-wide cache of rows. ## How a dashboard ends up with four identical queries The usual causes, in the order they show up in code review: 1. **A helper that builds a fresh QuerySet on every call.** `open_orders()` returns `Order.objects.filter(status='open')`; calling it four times makes four objects and four SELECTs. 2. **Cloning an evaluated QuerySet.** `orders.all()` and `orders.filter(...)` return new QuerySets; they do not inherit the parent's cache, so they query again even when the parent already holds the rows. 3. **Template attribute chains.** `{% if user.orders.all %}`, `{% for o in user.orders.all %}` and `{{ user.orders.all|length }}` each call the related manager's `all()` and get a new QuerySet, so each line queries. 4. **Indexing an unevaluated QuerySet.** `orders[0]` and `orders[0]` again run two `LIMIT 1` queries, because index and slice access do not fill the cache. ## The fix - Build the QuerySet **once** and keep it in a variable. - Evaluate it **once**, deliberately: iterate it, call `list()`, or test `if orders:` when you will loop over the rows anyway. - Pass **the same object** into the template context and helpers. The template's `{% if %}`, `{% for %}` and `|length` then all read the filled cache. - Narrow already-loaded rows in Python (`[o for o in orders if o.total > 100]`) instead of calling `filter()`, which would query again. - In templates, use `{% with orders=user.orders.all %}` to bind one QuerySet for the block, or pass it from the view. ## When not to load the rows at all Caching only helps when you need the rows. If the dashboard shows a number, `Order.objects.filter(status='open').count()` sends one `COUNT(*)` instead of transferring every row; if it shows a total, an aggregate computes it in the database. Mixing strategies carelessly also doubles work: `if orders.exists():` followed by a loop over `orders` runs two queries, while `if orders:` followed by the loop runs one. | Code | Queries | |---|---| | `open_orders()` called four times | 4 | | `orders = open_orders()`; `if orders:`, loop, `len(orders)`, `sum(...)` | 1 | | `if orders.exists():` then loop | 2 | | `orders.filter(total__gt=100)` after evaluation | +1 | | `orders.count()` after evaluation | +0 | ## The template-side version of the bug The view may be innocent and the template may make the copies: - `{% if request.user.orders.all %}` builds QuerySet one and evaluates it. - `<h2>{{ request.user.orders.all|length }} open</h2>` builds QuerySet two. - `{% for o in request.user.orders.all %}` builds QuerySet three. - `{{ request.user.orders.all.0 }}` builds QuerySet four and runs a `LIMIT 1`. Each attribute chain is resolved afresh, so each line gets its own object. Two fixes work: evaluate the orders in the view and pass one object as `orders`, or wrap the block in `{% with orders=request.user.orders.all %}` so every tag inside refers to the same QuerySet. After that change, the `if`, `length`, `for` and `.0` all read the cache filled by the first full evaluation. ## Confirming it Measure before and after. In a test, `self.assertNumQueries(1)` from Django's `TestCase` around the view call fails loudly if someone reintroduces a clone. In development, `django.db.connection.queries` lists the statements run when `DEBUG` is on. Look for identical SQL strings appearing several times; the result cache rule tells you that some line built a new QuerySet for each of them. ## Caveats - A long-lived cache is stale by design: the rows reflect the moment of evaluation. Inside one request that is what you want; across requests it is a bug. - Loading every row to call `len()` on a large table is worse than a `COUNT(*)`; reuse the cache only when the rows are needed anyway.

Why does a class-based ListView with queryset = Order.objects.filter(status='open') not serve stale rows across requests?
`MultipleObjectMixin.get_queryset()` calls `.all()` on a `QuerySet` class attribute before using it, so each request works on a fresh clone with an empty cache. Without that clone, the class attribute's cache would fill on the first request and every later request in the process would reuse those rows.
The dashboard needs the ten newest orders and the total count; how many queries is reasonable?
Two: `orders.count()` for the total (a `COUNT(*)`) and `orders[:10]` iterated for the list (a `LIMIT 10` SELECT). Loading every open order just to count them and keep ten would transfer the whole table, so reusing one cache is not the goal here; matching each query to what the page shows is.

saying these in an interview costs you the question

Django caches identical SQL across QuerySets within a request
orders.all() reuses the parent's already loaded rows
Filtering an evaluated QuerySet filters the cached rows in memory
Checking exists() before looping is always the efficient pattern
Template lookups like user.orders.all are evaluated only once per page

A Django dashboard view runs the same SELECT four times; how does the QuerySet result cache explain it, and what fixes it?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~3 min

Where the result cache lives

How a dashboard ends up with four identical queries

The fix

When not to load the rows at all

The template-side version of the bug

Confirming it

Caveats

▾common follow-ups2 questions

▾red flags5 flags

Where the result cache lives

How a dashboard ends up with four identical queries

The fix

When not to load the rows at all

The template-side version of the bug

Confirming it

Caveats

answer

go deeper