perf: N+1 query in _build_card_state_response for image_url #199

New Issue

cal · 2026-04-08T00:19:44Z

cal commented

2026-04-08 00:19:44 +00:00

Problem

In app/routers_v2/refractor.py lines 71–86, _build_card_state_response triggers a separate CardModel.get() query to resolve image_url from the variant card row for every card state in the response.

When listing cards via the /cards endpoint with limit=100, this means up to 100 additional DB queries.

Fix

Join the image_url lookup into the main query, or cache variant card image URLs on the RefractorCardState model itself (populated at variant creation time).

Impact

Latency scales linearly with result count. Becomes noticeable at 50+ cards per team as refractor states accumulate.

## Problem In `app/routers_v2/refractor.py` lines 71–86, `_build_card_state_response` triggers a separate `CardModel.get()` query to resolve `image_url` from the variant card row for every card state in the response. When listing cards via the `/cards` endpoint with `limit=100`, this means up to 100 additional DB queries. ## Fix Join the image_url lookup into the main query, or cache variant card image URLs on the `RefractorCardState` model itself (populated at variant creation time). ## Impact Latency scales linearly with result count. Becomes noticeable at 50+ cards per team as refractor states accumulate.

Claude added the

ai-working

label 2026-04-08 02:01:21 +00:00

cal referenced this issue from a commit

2026-04-08 02:04:40 +00:00

perf: batch image_url prefetch in list_card_states to eliminate N+1 (#199)

Claude referenced a pull request that will close this issue

2026-04-08 02:04:52 +00:00

perf: batch image_url prefetch in list_card_states to eliminate N+1 (#199) #206

Claude added

ai-pr-opened

and removed

ai-working

labels 2026-04-08 02:05:00 +00:00

Claude commented

2026-04-08 02:05:03 +00:00

PR #206 opens the fix: #206

The approach: before the response-building loop in list_card_states, collect all variant player IDs from the page and issue at most 2 bulk SELECT … WHERE player_id IN (…) queries (one for BattingCard, one for PitchingCard). Results are stored in a (player_id, variant) → image_url dict and passed directly to _build_card_state_response via a new image_url parameter, skipping the per-row CardModel.get() entirely. Query count goes from 1 + N to 1 + 0–2 regardless of page size.

PR #206 opens the fix: https://git.manticorum.com/cal/paper-dynasty-database/pulls/206 The approach: before the response-building loop in `list_card_states`, collect all variant player IDs from the page and issue at most 2 bulk `SELECT … WHERE player_id IN (…)` queries (one for BattingCard, one for PitchingCard). Results are stored in a `(player_id, variant) → image_url` dict and passed directly to `_build_card_state_response` via a new `image_url` parameter, skipping the per-row `CardModel.get()` entirely. Query count goes from `1 + N` to `1 + 0–2` regardless of page size.

cal closed this issue

2026-04-08 02:25:53 +00:00

cal referenced this issue from a commit

2026-04-08 02:25:54 +00:00

Merge pull request 'perf: batch image_url prefetch in list_card_states to eliminate N+1 (#199)' (#206) from issue/199-perf-n-1-query-in-build-card-state-response-for-im into main

Sign in to join this conversation.

No Milestone

No project

No Assignees

2 Participants

Notifications

Due Date

The due date is invalid or out of range. Please use the format 'yyyy-mm-dd'.

No due date set.

Dependencies

No dependencies set.

Reference: cal/paper-dynasty-database#199