Fix N+1 query pattern in standings recalculation #75

New Issue

cal · 2026-03-27T04:06:40Z

cal commented

2026-03-27 04:06:40 +00:00

Problem

In app/db_engine.py lines 1304-1343, the standings recalculation loops through every game and calls game.update_standings(), which issues ~4 queries each. For a full season (~288 games), this generates 1,100+ queries.

On PostgreSQL with network latency between containers, this is significantly slower than on SQLite (in-process).

Fix

Batch the standings updates — prefetch related data and use bulk updates instead of row-by-row processing.

Severity

Medium — performance issue that worsens with data volume and network latency.

## Problem In `app/db_engine.py` lines 1304-1343, the standings recalculation loops through every game and calls `game.update_standings()`, which issues ~4 queries each. For a full season (~288 games), this generates 1,100+ queries. On PostgreSQL with network latency between containers, this is significantly slower than on SQLite (in-process). ## Fix Batch the standings updates — prefetch related data and use bulk updates instead of row-by-row processing. ## Severity Medium — performance issue that worsens with data volume and network latency.

Claude added the

ai-working

label 2026-03-27 08:31:14 +00:00

cal referenced this issue from a commit

2026-03-27 08:37:54 +00:00

fix: batch standings updates to eliminate N+1 queries in recalculate (#75)

Claude referenced a pull request that will close this issue

2026-03-27 08:38:07 +00:00

fix: batch standings updates to eliminate N+1 queries in recalculate (#75) #93

Claude added the

ai-pr-opened

label 2026-03-27 08:38:12 +00:00

Claude commented

2026-03-27 08:38:17 +00:00

PR #93 opened: #93

Approach: Pre-fetch all standings, teams, and divisions for the season into dicts before the game loop, inline the win/loss tallying logic using dict lookups (zero DB queries per game), then save all modified standings in a single bulk_update call. Full-season recalculation drops from ~1,100+ queries to ~5 queries. StratGame.update_standings() is left intact for single-game use cases.

PR #93 opened: https://git.manticorum.com/cal/major-domo-database/pulls/93 **Approach**: Pre-fetch all standings, teams, and divisions for the season into dicts before the game loop, inline the win/loss tallying logic using dict lookups (zero DB queries per game), then save all modified standings in a single `bulk_update` call. Full-season recalculation drops from ~1,100+ queries to ~5 queries. `StratGame.update_standings()` is left intact for single-game use cases.

Claude removed the

ai-working

label 2026-03-27 08:38:19 +00:00

Sign in to join this conversation.