Admin workshop show is slow (N+1 + 4,337-item dropdown) — 92s for /admin/workshops/3824

[mroderick]

Summary

Admin::WorkshopsController#show (/admin/workshops/:id) is pathologically slow for large workshops. APM flagged 39,349 ms at Thu 12:46:00; a local reproduction measured 1m 32s.

Production / local log for /admin/workshops/3824 (London, 2026-07-29, 27 attending — 14 students + 13 coaches — 4,364 total invitations):

Completed #show -- { path: "/admin/workshops/3824", status: 200, view_runtime: 40274.78, db_runtime: 50970.58, queries_count: 210, cached_queries_count: 39, allocations: 221203962, gc_time: 7930.2 }

Symptom

• ~92 s total response for a single admin page with 27 attending members.
• 51 s DB time across 210 queries, 40 s view time, ~221 M object allocations.

Root cause (two compounding problems)

1. N+1 queries in the attendance rows (drives the 51 s DB time)

set_admin_workshop_data (app/controllers/concerns/admin/workshop_concerns.rb) preloads member, member_notes + authors, attendance_warnings, and overrider via with_notes_and_their_authors. It does not preload the member's workshop_invitations, which is exactly what each row in app/views/admin/workshops/_attendance_row.html.haml re-queries:

View call	Method	Queries/row	Queries
member.newbie?	MemberPresenter#newbie?	~1	workshop_invitations.attended.exists?
member.flag_to_organisers?	Member#multiple_no_shows? + attendance_warnings.last_six_months	~3	2× scoped workshop_invitations (taken_place/last_six_months/.attended) + 1× attendance_warnings scope (bypasses the preload)
member.recent_notes.any?	Member#recent_notes	~2	workshop_invitations.order_by_latest.attended.take(5) + fresh member_notes.where(...) (bypasses the preload)

Measured against the production dump with a SQL-notification counter: ~6 queries × 27 attending rows ≈ 159 queries from these three methods alone. Each query runs a date-scoped scan/join over the large workshop_invitations table (4,364 rows for this workshop; ~200k+ total), so they are individually slow — the 51 s accumulates across rows.

2. Massive view allocations (drives the 40 s view time / 221 M allocations)

• The RSVP dropdown builds a <select> from 4,337 not_accepted invitations: @workshop.invitations.includes(:member).not_accepted.all.map { |u| ["#{u.member.full_name} (#{u.role})", u.token] } in _invitation_management.html.haml. One query, but ~4,337 objects plus HAML render time for an unusable select.
• Presenter re-wrapping and per-row string building compound this across all rows.

How to reproduce

Pointed at the production dump, log in as an admin and open either URL (works 3824 has 27 attending, 1749 has 59 — worse):

• http://localhost:3000/admin/workshops/3824
• http://localhost:3000/admin/workshops/1749 (59 attending, 4,827 invites)

Proposed fix (separate implementation session)

Kill the N+1 — collapse the three per-row checks into ~3 aggregate queries keyed by the attending members' ids, computed once, and memoise per member:

• newbie? → one WorkshopInvitation.where(member_id: ids, attended: true) grouped by member.
• flag_to_organisers? → one workshop_invitations.taken_place.last_six_months.accepted grouped by member + one attendance_warnings query, aggregated in Ruby.
• recent_notes → one MemberNote.where(member_id: ids, created_at: ...) query.
• Reuse a single MemberPresenter per member instead of re-wrapping on every row call.

Projected: 210 queries → ~15, endpoint from ~92 s → well under 1 s.

Tame the dropdown (second, lower-priority pass): the 4,337-item <select> is poor UX regardless of performance. Paginate it, replace with a searchable field, or show a count + link.

Acceptance criteria

• /admin/workshops/3824 (and a larger workshop such as 1749) renders in well under ~1 s.
• Query count for the show page drops from ~210 to ~<20.
• Object allocations drop materially (no 4,337-item dropdown and no per-row re-queries).