Killing N+1 Queries in a Logistics Dashboard Without Guessing

One of the easiest lies a backend can tell is "the page loads."

That sentence hides a lot. It can mean the page loads in 180 milliseconds. It can also mean it loads in 3.8 seconds after firing off dozens of queries and serializing half the ORM along the way.

When we started profiling our dashboard and related delivery pages, we found the usual pattern: features had shipped incrementally, the pages technically worked, and query counts had drifted into territory nobody would have defended if the numbers had been visible from the start.

So we stopped guessing and instrumented the problem first.

1. Count the queries before you try to optimize them

We added a simple QueryCounter context manager around SQLAlchemy's before_cursor_execute event:

class QueryCounter:
    def __enter__(self):
        from sqlalchemy import event
        from app.extensions import db

        def _on_execute(conn, cursor, statement, parameters, context, executemany):
            self.count += 1
            self.queries.append({
                "sql": str(statement)[:500],
                "time": time.time(),
            })

        self._listener = _on_execute
        event.listen(db.engine, "before_cursor_execute", self._listener)
        return self

And when the block exits, it logs the count and warns on the suspicious cases:

if self.count > 15:
    logger.warning(
        f"Possible N+1 in '{self.label}': {self.count} queries. "
        f"Consider eager loading or query optimization.",
        extra={"query_count": self.count},
    )

That changed the conversation immediately. "Dashboard feels kind of heavy" became "this request just did 41 queries."

Measurements beat vibes every time.

2. Replace repetitive per-row work with grouped queries

A lot of performance issues in dashboards are not one huge bad query. They are lots of small, reasonable queries done repeatedly.

In ReportService.get_user_dashboard_stats, we moved several calculations into grouped aggregate queries instead of asking the database similar questions over and over:

delivery_data = (
    db.session.query(
        func.date(Delivery.delivery_date).label("date"),
        func.sum(DeliveryItem.price * DeliveryItem.quantity).label("sales"),
    )
    .join(DeliveryItem, Delivery.id == DeliveryItem.delivery_id)
    .filter(
        Delivery.user_id == user.id,
        func.date(Delivery.delivery_date) >= start_date,
        func.date(Delivery.delivery_date) <= end_date,
    )
    .group_by(func.date(Delivery.delivery_date))
    .all()
)

The corresponding returns query follows the same shape. From there, we build dictionaries in memory and assemble the seven-day chart data without asking the database fourteen separate questions.

That is a useful pattern in operational products: let the database do set-based work, then shape the response once in Python.

3. Eager load the relations you know the template will touch

Some of the worst query inflation came from recent-activity and list views where templates accessed related objects after the initial query had already returned.

We fixed that with explicit eager loading:

recent_deliveries_query = (
    Delivery.query.filter_by(user_id=user.id)
    .options(
        _joinedload(Delivery.supermarket),
        _joinedload(Delivery.items),
    )
    .order_by(Delivery.delivery_date.desc())
    .limit(10)
    .all()
)

We use the same idea elsewhere in service and route layers with joinedload() and selectinload() depending on the access pattern. The important part is not which loader strategy sounds more sophisticated. The important part is loading the data you know the request is going to touch before the template starts walking relationships.

4. Cache the expensive dashboard result, but keep the invalidation obvious

The dashboard is exactly the kind of surface where caching earns its keep. The trick is doing it without turning freshness into a mystery.

Our pattern is Redis first, in-memory fallback second:

cache_key = f"dashboard_stats:{user.id}"
cache = _get_redis_cache()
if cache:
    cached_data = cache.get(cache_key)
    if cached_data is not None:
        return cached_data

if cache_key in _dashboard_cache:
    cached_data, cached_time = _dashboard_cache[cache_key]
    if time.time() - cached_time < 300:
        return cached_data

That gives us shared cache behavior when Redis is available and a safe fallback when it is not. The TTL is five minutes, which is short enough for operational usefulness and long enough to keep the dashboard from recomputing on every page load.

The other half of the pattern is explicit invalidation:

@staticmethod
def invalidate_dashboard_cache(user_id: int):
    cache_key = f"dashboard_stats:{user_id}"
    cache = _get_redis_cache()
    if cache:
        cache.delete(cache_key)
    _dashboard_cache.pop(cache_key, None)

If you create or delete deliveries and returns without invalidating, you do not have a cache. You have a stale-data delivery mechanism.

5. Do not cache ORM objects you cannot safely rehydrate

One detail that saved us later: for recent activity, we convert ORM instances to plain dictionaries while the session is still active.

for delivery in recent_deliveries_query:
    recent_activity.append({
        "__tablename__": "delivery",
        "id": delivery.id,
        "supermarket": {
            "name": delivery.supermarket.name if delivery.supermarket else "Unknown"
        },
        "total_value": float(delivery.total_value),
        "activity_date": delivery.delivery_date,
        "activity_type": "delivery",
    })

That avoids detached-instance problems after pulling the object graph back out of cache later. It also forces us to be honest about the response shape we actually need.

That small discipline is worth keeping in mind any time you are tempted to cache "whatever the ORM gave me."

6. Lock the gains in with tests

Performance fixes are easy to celebrate once and lose six weeks later.

So we added tests around the cache behavior and the latency expectation. For example:

start2 = time.time()
ReportService.get_user_dashboard_stats(auth_user)
time_cached = time.time() - start2

assert time_cached < time_uncached / 10
assert time_cached < 0.01

We also test cache invalidation, expiry, size limits, and isolation between users. Those tests are not perfect performance guarantees, but they stop the obvious regressions from sneaking back in during normal feature work.

What this changed for us

The practical outcome was not subtle.

Query counts dropped. Dashboard loads became predictable. The app felt calmer under normal use. And once the instrumentation existed, performance work got much cheaper because we could see which page was drifting before a user had to complain about it.

The larger engineering lesson is straightforward: N+1 problems survive in codebases where nobody is counting. The moment you start measuring query counts, grouping repetitive work, eagerly loading what the request really needs, and testing the cache behavior, the problem becomes much more ordinary.

Which is exactly what you want. Ordinary problems get fixed.