The usual culprit is doing expensive work on every click. A real example: a 230-million-row dashboard (largest table 91M rows, 16GB DuckDB) loaded in 9-12 seconds; it now runs under a second on most pages. The single biggest lever was pre-compute. A query that JOINs and aggregates across tens of millions of rows on each request should instead be run once into a flat, denormalized, indexed table - then the live query just reads the result. (In one case DuckDB was doing a full sequential scan of 12M rows to find ~200 matches because the JOIN could not use the index; pre-computing the JOIN into a person_filmography table killed the 1.6s cost.)
Just as important is the method: add a timer to each component, measure the page box by box, tackle the single slowest one, then measure again. One problem at a time - across 14 bottlenecks. This is exactly the kind of methodical measure-diagnose-fix loop an AI coder (Claude Code here) is good at: writing test scripts, timing queries with curl and Python, and tracing why an index is not being used.
Full breakdown of all 14 fixes: https://www.tigzig.com/post/from-12-second-queries-to-under-1s-optimizing-230-million-row-dashboard. Related: how to build a DuckDB + FastAPI dashboard for large data https://www.tigzig.com/agents-faq/how-to-build-a-duckdb-fastapi-dashboard-for-large-data.
← All Agents FAQ