Query cost
Query cost is best counted in statements rather than measured in seconds, because a threshold in seconds measures the machine you happened to run on. A statement count is the same on an empty test database as in production, which is what makes it able to catch the fault that only hurts once a workspace has grown.
Why seconds are the wrong unit#
A page that issues one query per row is fast on a development database with forty rows and slow on a real one with forty thousand. Timing it locally produces a green result; timing it in production produces an alert weeks later, attributed to growth rather than to the code that has always been wrong.
Counting statements removes the machine from the measurement. A test can assert that rendering a page costs a fixed number of statements regardless of how many rows it shows, and that assertion fails the moment somebody reintroduces a per-row fetch — on a laptop, in a second, before the change is merged.
The shape of the fault#
It is called N+1: one query to fetch a list, then one more per item to fetch something each item needs. It arrives honestly — the per-item fetch is usually a single attribute access that looks free — and it compounds when two of them nest.
The cure is set-based: fetch the related rows for the whole page in one windowed query, fetch the referenced records by their identifiers in one more, and join them in memory. Counting is a GROUP BY, not a loop. Filtering and paging belong in SQL, not in the language reading the results.
Four measured examples#
These are counted statements from this system, before and after the fix. They are measurements rather than estimates.
| Screen | Before | After | What changed |
|---|---|---|---|
| The Owner's customer list | 5,574 | 3 | The user, workspace and entitlement were fetched one at a time per membership. Three inner joins say the same thing |
| The triage strip | 83 | 11 | A page of threads, then the last message and the contact per row. One windowed last-message query plus one contacts-by-id query, flat with page size |
| The workspace console | 89 | 17 | The same fault on the shared console path, fixed by the same shared page_context |
| Home | 133 | 15 | The newest open threads plus three statements per thread. Three set-based statements, flat |
The first row is the one that shows why the unit matters. Nothing about 5,574 statements is visible on a small database; the cost was proportional to the number of accounts, and the fault only became a problem at the size where a fix is most disruptive.
Reading in Python what the database should have done#
The related fault is not per-row queries but per-page slices: read a fixed number of rows, then count, filter, search and page over that slice in the application. The statement count looks fine. The answers are wrong.
A conversation list built that way read 400 threads and did everything else in memory. On a workspace with 430 conversations whose 25 closed ones were the oldest, the Closed tab read 0, the total read 400, and page five came back empty — three separate wrong answers from one design decision. Counts are a single GROUP BY now, the filter and the page are SQL, and a % or _ typed into the search box is escaped and matched as a character rather than as a wildcard.
Does Connect use statement counting?#
Yes. Query cost in this codebase is discussed, tested and reported in statements, and the four numbers above are recorded as measured facts rather than as impressions. The shared paths that fixed them — one workspace_console.page_context serving both the triage strip and the console — are also the reason a single fix could apply to both audiences at once.
What Connect does not publish is a latency figure for a screen. A millisecond number would describe a particular machine on a particular day, and would go stale without anybody noticing; a statement count describes the code.
Questions#
Why not just add an index and move on?
An index makes each statement cheaper. It does not reduce how many statements there are, and a per-row fetch pattern issues one round trip per row no matter how fast each one is. Round trips are the cost that grows with the business, so the count is the thing to fix first.
Does a lower statement count always mean a better page?
No. One enormous query that scans everything can be worse than several small ones. What matters is that the count does not grow with the number of rows displayed — flat beats small — and that each statement is bounded by an index and a window rather than by the size of the table.
How does this affect me as someone using Connect?
Mostly by what you do not see. The screens that used to slow down as a workspace grew now cost the same at forty rows as at four hundred, and the counting faults that produced confident wrong totals — an empty Closed tab, a page five with nothing on it — came from the same design and went with it.