Remove N+1 queries in org, schedule, user and tag code paths #79

New issue

Open

opened 2026-10-03 19:44:44 +00:00 by jalenluorion · 0 comments

jalenluorion commented

2026-10-03 19:44:44 +00:00

Member

What's wrong
Several routes run one or more queries per row of a previous query. With remote Supabase every round trip costs tens of ms, so large orgs and schedules load slowly.

How to reproduce
Turn on SQL logging (echo=True in api/app/services/db.py, or a SQLAlchemy before_cursor_execute counter in a test), call GET /api/organizations/org/<id> for an org with many categories, and count the statements. The count grows with the number of categories.

Expected
A fixed number of queries per request, independent of row count: one query per table with IN (...) or a join, then grouping in Python.

Where to look

api/app/api/organizations.py get_organization_data() L99-130: events plus occurrences per category.
api/app/api/schedule.py get_schedule_route() L60-160: categories per org, then events plus occurrences per category, in both the course and the club branches.
api/app/api/organizations.py get_admins_in_org() L623-625: a user lookup and an org lookup per admin (the org is the same every time).
api/app/api/users.py get_admin_categories() L184: join_org_and_to_dict() per category; get_user_schedules() L158-171 lazy-loads schedule_orgs and org per row.
api/app/api/events.py tag lookups per tag: create_event_record() L197-207 and update_event() L831-842.
Line numbers are from main at 2c5f2e9.

Done when

each location above is fixed, with responses unchanged
a test per route in api/tests/api/test_query_counts.py asserting the statement count does not grow with the number of categories, admins or tags

**What's wrong** Several routes run one or more queries per row of a previous query. With remote Supabase every round trip costs tens of ms, so large orgs and schedules load slowly. **How to reproduce** Turn on SQL logging (`echo=True` in `api/app/services/db.py`, or a SQLAlchemy `before_cursor_execute` counter in a test), call `GET /api/organizations/org/<id>` for an org with many categories, and count the statements. The count grows with the number of categories. **Expected** A fixed number of queries per request, independent of row count: one query per table with `IN (...)` or a join, then grouping in Python. **Where to look** - [ ] `api/app/api/organizations.py` `get_organization_data()` L99-130: events plus occurrences per category. - [ ] `api/app/api/schedule.py` `get_schedule_route()` L60-160: categories per org, then events plus occurrences per category, in both the course and the club branches. - [ ] `api/app/api/organizations.py` `get_admins_in_org()` L623-625: a user lookup and an org lookup per admin (the org is the same every time). - [ ] `api/app/api/users.py` `get_admin_categories()` L184: `join_org_and_to_dict()` per category; `get_user_schedules()` L158-171 lazy-loads `schedule_orgs` and `org` per row. - [ ] `api/app/api/events.py` tag lookups per tag: `create_event_record()` L197-207 and `update_event()` L831-842. - Line numbers are from main at 2c5f2e9. **Done when** - [ ] each location above is fixed, with responses unchanged - [ ] a test per route in `api/tests/api/test_query_counts.py` asserting the statement count does not grow with the number of categories, admins or tags

jalenluorion added this to the Phase 3 milestone

2026-10-03 19:44:44 +00:00

jalenluorion added the

chore

data

intermediate

labels

2026-10-03 19:44:44 +00:00

jalenluorion added this to the CMUCal project

2026-10-03 19:45:09 +00:00