perf(statistics): deck routes take 2–30 s on real data (P1) #157

New issue

Open

opened 2026-09-27 15:24:23 +00:00 by gabogg · 0 comments

gabogg commented

2026-09-27 15:24:23 +00:00

Owner

Priority: P1. The statistics deck is too slow to present from on real data.

Evidence

Master @ 92fe600, run locally on a copy of the prod DB (236 MB, data from 2026-09-03 to 2026-09-26), with HikCentral calls blocked. Browser sweep as operator; single-route timings from curl:

Route	Time
`GET /periods/week/2026-09-14/summary`	4.7 s
`GET /timeseries/daily?start=2026-09-14&end=2026-09-20`	4.8 s
`GET /timeseries/daily?…&bucket=hour` (week heatmap)	6.6 s
`GET /timeseries/daily` (previous week)	2.7 s
`GET /dwell/dayparts?day=…&baseline=usual_weekday`	2.0 s
`GET /periods/day/…/summary`	1.7 s
`GET /periods?granularity=day&limit=100`	0.4 s

Time until a view stops showing LOADING in the browser:

Day: median 3.1 s, up to 10.4 s in Room.
Week: median 11.6 s. The first Week Room load was still LOADING after 30 s.

The view's parallel requests look serialised on the server, so a Room view costs roughly the sum of its routes. On top of that, every profile switch re-renders and refetches (Day and Month keep no route cache). A presenter switching Room/Desk/Laptop or stepping periods waits several seconds each time.

Open questions (needs triage)

Where is the time spent? Query plans and indexes on the event and cycle-quality tables, per-period recomputation (quality, baselines, calibration), or synchronous SQLite work blocking the event loop. Profile before choosing.
Fix strategy, one or more of:
- query and index work;
- caching closed-period results server-side (closed periods don't change);
- a precomputed per-day aggregate table;
- client-side caching across profile switches, since a profile change needs no new data.
Target: every view interactive in 1 s or less for any closed period.

Related: #156 (view consolidation; a shared per-period cache fits there).

Maintainer triage — 2026-09-27

Highest-priority implementation work. Profile against the existing production-copy reproduction before selecting optimizations; retain the target of every closed-period view interactive within 1 second. Record route and browser timings before/after, including first load and profile switching. Closed periods are not immutable: any cache must invalidate for verdict edits, applicable schedule changes, backfills and late ingestion. Coordinate with #156 without requiring its entire scope first.

**Priority: P1.** The statistics deck is too slow to present from on real data. ## Evidence Master @ 92fe600, run locally on a copy of the prod DB (236 MB, data from 2026-09-03 to 2026-09-26), with HikCentral calls blocked. Browser sweep as operator; single-route timings from `curl`: | Route | Time | |---|---| | `GET /periods/week/2026-09-14/summary` | 4.7 s | | `GET /timeseries/daily?start=2026-09-14&end=2026-09-20` | 4.8 s | | `GET /timeseries/daily?…&bucket=hour` (week heatmap) | 6.6 s | | `GET /timeseries/daily` (previous week) | 2.7 s | | `GET /dwell/dayparts?day=…&baseline=usual_weekday` | 2.0 s | | `GET /periods/day/…/summary` | 1.7 s | | `GET /periods?granularity=day&limit=100` | 0.4 s | Time until a view stops showing LOADING in the browser: - Day: median 3.1 s, up to 10.4 s in Room. - Week: median 11.6 s. The first Week Room load was still LOADING after **30 s**. The view's parallel requests look serialised on the server, so a Room view costs roughly the sum of its routes. On top of that, every profile switch re-renders and refetches (Day and Month keep no route cache). A presenter switching Room/Desk/Laptop or stepping periods waits several seconds each time. ## Open questions (needs triage) - Where is the time spent? Query plans and indexes on the event and cycle-quality tables, per-period recomputation (quality, baselines, calibration), or synchronous SQLite work blocking the event loop. Profile before choosing. - Fix strategy, one or more of: - query and index work; - caching closed-period results server-side (closed periods don't change); - a precomputed per-day aggregate table; - client-side caching across profile switches, since a profile change needs no new data. - Target: every view interactive in 1 s or less for any closed period. Related: #156 (view consolidation; a shared per-period cache fits there). ## Maintainer triage — 2026-09-27 Highest-priority implementation work. Profile against the existing production-copy reproduction before selecting optimizations; retain the target of every closed-period view interactive within 1 second. Record route and browser timings before/after, including first load and profile switching. Closed periods are not immutable: any cache must invalidate for verdict edits, applicable schedule changes, backfills and late ingestion. Coordinate with #156 without requiring its entire scope first.

gabogg added the

needs-triage

label

2026-09-27 15:24:23 +00:00

gabogg added

high-priority

ready-for-agent

and removed

needs-triage

labels

2026-09-27 16:29:10 +00:00

gabogg referenced this issue