perf: N+1 sequential player/creator lookups in loops #89

New Issue

cal · 2026-03-20T13:17:09Z

cal commented

2026-03-20 13:17:09 +00:00

Problem

Multiple service methods loop over a list and make one API call per item sequentially.

`services/decision_service.py` lines 127–143 (`find_winning_losing_pitchers`)

wp = await player_service.get_player(wp_id)
lp = await player_service.get_player(lp_id)
sv = await player_service.get_player(sv_id)
for hold_id in hold_ids:
    holder = await player_service.get_player(hold_id)  # N+1
for bsv_id in bsv_ids:
    bsv = await player_service.get_player(bsv_id)      # N+1

5–10 sequential calls per game that are all independent.

`services/custom_commands_service.py` lines 463–707

Three methods (get_popular_commands, get_commands_needing_warning, get_commands_eligible_for_deletion) each fetch a list then loop calling get_creator_by_id() per item:

for cmd_data in commands_data:
    creator = await self.get_creator_by_id(cmd_data.creator_id)  # N+1

Fix

Gather all lookups in parallel:

all_ids = [wp_id, lp_id, sv_id] + hold_ids + bsv_ids
players = await asyncio.gather(*[player_service.get_player(pid) for pid in all_ids if pid])

For custom_commands, either gather creator lookups or use the API's embedded-creator response format (already used in get_commands_by_creator).

Impact

HIGH (decision_service — game processing path), MEDIUM (custom_commands — admin)

## Problem Multiple service methods loop over a list and make one API call per item sequentially. ### `services/decision_service.py` lines 127–143 (`find_winning_losing_pitchers`) ```python wp = await player_service.get_player(wp_id) lp = await player_service.get_player(lp_id) sv = await player_service.get_player(sv_id) for hold_id in hold_ids: holder = await player_service.get_player(hold_id) # N+1 for bsv_id in bsv_ids: bsv = await player_service.get_player(bsv_id) # N+1 ``` 5–10 sequential calls per game that are all independent. ### `services/custom_commands_service.py` lines 463–707 Three methods (`get_popular_commands`, `get_commands_needing_warning`, `get_commands_eligible_for_deletion`) each fetch a list then loop calling `get_creator_by_id()` per item: ```python for cmd_data in commands_data: creator = await self.get_creator_by_id(cmd_data.creator_id) # N+1 ``` ## Fix Gather all lookups in parallel: ```python all_ids = [wp_id, lp_id, sv_id] + hold_ids + bsv_ids players = await asyncio.gather(*[player_service.get_player(pid) for pid in all_ids if pid]) ``` For custom_commands, either gather creator lookups or use the API's embedded-creator response format (already used in `get_commands_by_creator`). ## Impact **HIGH** (decision_service — game processing path), **MEDIUM** (custom_commands — admin)

cal added the

ai-working

label 2026-03-20 13:18:36 +00:00

cal removed the

ai-working

label 2026-03-20 13:21:31 +00:00

Claude referenced this issue

2026-03-20 18:09:27 +00:00

perf: eliminate redundant GET after create/update and parallelize stats (#95) #112

Claude referenced this issue

2026-03-20 18:09:40 +00:00

perf: redundant GET after create/update in custom_commands_service #95

Claude added the

ai-working

label 2026-03-20 20:01:23 +00:00

Claude added the

status/in-progress

label 2026-03-20 20:02:30 +00:00

cal referenced this issue from a commit

2026-03-20 20:04:41 +00:00

perf: parallelize N+1 player/creator lookups with asyncio.gather (#89)

Claude referenced a pull request that will close this issue

2026-03-20 20:04:55 +00:00

perf: parallelize N+1 player/creator lookups with asyncio.gather (#89) #118

Claude added

status/pr-open

and removed

status/in-progress

labels 2026-03-20 20:05:05 +00:00

Claude commented

2026-03-20 20:05:10 +00:00

Opened PR #118: #118

Fix approach:

decision_service.find_winning_losing_pitchers: all IDs (wp, lp, sv, holds, blown saves) are placed in a single ordered list and fetched in one asyncio.gather(). asyncio.sleep(0, result=None) serves as a no-op for None IDs, preserving the None sentinel without a branch on the gather list.
custom_commands_service (3 methods): get_creator_by_id() calls gathered with return_exceptions=True. Post-gather loop preserves original behavior: BotException → log+skip; other exceptions → re-raise.

977 passed, 2 skipped.

Opened PR #118: https://git.manticorum.com/cal/major-domo-v2/pulls/118 **Fix approach:** - `decision_service.find_winning_losing_pitchers`: all IDs (wp, lp, sv, holds, blown saves) are placed in a single ordered list and fetched in one `asyncio.gather()`. `asyncio.sleep(0, result=None)` serves as a no-op for None IDs, preserving the `None` sentinel without a branch on the gather list. - `custom_commands_service` (3 methods): `get_creator_by_id()` calls gathered with `return_exceptions=True`. Post-gather loop preserves original behavior: `BotException` → log+skip; other exceptions → re-raise. 977 passed, 2 skipped.

Claude added

ai-pr-opened

and removed

ai-working

labels 2026-03-20 20:05:15 +00:00

cal referenced this issue from a commit

2026-03-31 19:42:56 +00:00

perf: parallelize N+1 player/creator lookups with asyncio.gather (#89)

cal closed this issue

2026-03-31 19:45:02 +00:00

cal referenced this issue from a commit

2026-03-31 19:45:03 +00:00