Summary#
The question a dashboard asks — how many, how much, how many per group — is a question the database answers, reading no rows at all. Reading every row to answer it in the page is what took an app down on 2026-10-29: its overview read 29,516 rows on first paint and counted them in the interpreter, seconds of CPU on every call.
enum Region { North, South, East, West }
entity Ticket {
[Required, MaxLength(200)] string Title;
Region Region;
bool Open;
decimal Hours;
}
class RegionCount { public Region Region; public int Open; public decimal Hours; }
int OpenTickets() => Ticket.Where(t => t.Open).Count();
bool AnythingOpen() => Ticket.Any(t => t.Open);
decimal HoursLogged() => Ticket.Sum(t => t.Hours);
List<RegionCount> ByRegion() =>
Ticket.Where(t => t.Open)
.GroupBy(t => t.Region)
.Select(g => new RegionCount { Region = g.Key, Open = g.Count(), Hours = g.Sum(t => t.Hours) })
.ToList();Description#
What not to write: every row, counted in memory#
Ticket.ToList() followed by .Where(…), .Count, .GroupBy(…) or a counter bumped in a foreach brings
every row across from the database to work out one number. It is fast on a laptop with forty rows and it is the
outage at forty thousand. osy check flags it as cost-rows-counted-in-memory and names the query to write instead;
the read budget below stops it in osy test.
| in memory (every row read) | in the query (no row read) |
|---|---|
Ticket.ToList().Count | Ticket.Count() |
Ticket.ToList().Where(t => t.Open).Count() | Ticket.Where(t => t.Open).Count() |
foreach (var t in Ticket) { if (t.Open) n++; } | Ticket.Count(t => t.Open) |
Ticket.ToList().Sum(t => t.Hours) | Ticket.Sum(t => t.Hours) |
Ticket.ToList().GroupBy(t => t.Region)… | Ticket.GroupBy(t => t.Region).Select(g => new … { g.Key, N = g.Count() }) |
Showing the rows a person looks at#
A list on screen shows a page of rows, never the table. Order it, then page it — an unordered page can repeat a row on page two:
List<Ticket> Page(int n) => Ticket.Where(t => t.Open).OrderBy(t => t.Title).Skip(n * 50).Take(50).ToList();On a page the shortest way to show a table's rows is the kit's Rows: live var tickets = Ticket.OrderBy(t => t.Title);
then Rows(tickets) { t => Text(t.Title); } — or the query written in place, Rows(Ticket.OrderBy(t => t.Title)) { … }.
Leave off .Take and .ToList(): the list decides how much to read. It shows a page of 50 (pageSize: changes it) and
reads the next page when its end scrolls into view; each page is read just past the last row held, in the query's order,
so the hundredth page costs what the first did and a row added above the reader's place never shows twice. Order it by a
plain column (a name, a date, a number) and the page is stable; DataGrid pages the same way. A page of
Rows over ten thousand rows reads 50 of them on first paint, which a [PageBudget] can hold ([PageBudget]: what a page's first paint and live re-run may cost, held by osy test).
A figure that is expensive to work out: a stored roll-up#
When a number needs more than one aggregate query can give — a figure over a year of history, read on every page view — keep it in a row of its own and update it when the data changes, not when the page reads it. The page then reads one row.
entity TicketTally {
int Open;
}
void Opened() {
var tally = TicketTally.FirstOrDefault() ?? new TicketTally();
tally.Open = tally.Open + 1;
UnitOfWork.Commit();
}A read with no bound is reported where you write it#
osy validate prints READ_HAS_NO_BOUND at the line of every read whose size nobody chose — a function that
materialises an entity with no Take, a page list that does not page, a page read with no Take — with the bounded
form to write: .Take(50), .OrderBy(…).Skip(n).Take(m), Rows(query) (a list that pages itself), LoadMoreButton(rows), or the aggregate. It is a warning
today and becomes a compile error. A read that genuinely needs every row states its bound and reason on the line
before it with ReadBudget.Raise(rows, "why") (below), and a table that is small by its nature declares
[MaxRows(n)] — or, for one parent's rows, [MaxRows(n, Per = Parent)] (below). Those are the only ways to opt out,
because they are the only ones that say how many.
The same code covers a read through a Query<T> local (Query<Order> open = Order.Where(…); open.ToList()), a
class method, and a foreach over a query. It also covers the caller of a function that returns every row: calling
ListOrders(true).Any(o => o.Customer == name) reads and ships the whole table to learn about one row, so ask the
database instead — Order.Where(o => o.Open && o.Customer == name).Any() reads one. Helpers declared in a *.test.osy
file are bounded by their seed and are not judged.
The read budget#
Every request — a page load, a server call, a timer — and every [Test] may read 2,000 rows through the app's own
queries. An aggregate the database answers reads none; a page of 50 reads 50. Past the budget:
| where | what happens |
|---|---|
osy test | the test fails, naming the read as written and its file and line |
| the local dev host | the request fails the same way, so you meet it while writing the page |
| a deployed app | the read is served, and one warning per request goes to the app's own log (osy logs), naming the read |
| a deployed app, past 20,000 rows in one request | the request is refused, and an error goes to the app's own log naming the read and its line — never answered from part of the rows, because a total over part of them is silently wrong |
The database never sends the rows past the line: the budget left rides in the read's SQL as its limit, so a read past it stops one row past it rather than loading the whole table first.
A read that genuinely needs every row — an export, a migration — says so on the line before it. The raise covers that one read, and the reason is the point: it is what a reviewer reads.
int ExportRows() {
ReadBudget.Raise(50000, "the CSV export lists every ticket");
return Ticket.ToList().Count;
}A table that is small by its nature: [MaxRows(n)]#
Some tables are small because of what they are — the regions an app serves, its plans, its zones. Reading one whole is
correct, and writing Take or ReadBudget.Raise on every read of it would be noise. Say the bound once, on the entity:
[MaxRows(50)]
entity Desk {
[MaxLength(40)] string Name;
}
List<Desk> Desks() => Desk.OrderBy(d => d.Name).ToList();- The bound is enforced. The insert of row n+1 is refused with an error naming the entity and the bound
(
'Desk' holds at most 50 rows ([MaxRows(50)])), and nothing in that commit is written. A delete makes room. Two writers racing for the last free row cannot both get it: inserts into a bounded table take one lock per table and count under it, so the second counts the first's row and is refused. - Every read of it is bounded, so
READ_HAS_NO_BOUNDsays nothing about a function, a caller filtering its list, or a page list that reads it. A read of a subtype's base counts the subtype's rows too. - n is at most 2,000, the read budget — a larger number is a compile error, because a "small" table bigger than one
request may read is not small. Bound the reads of a table that grows (
.Take(n), a list that pages) instead. - Not on a
class(it has no rows) or an entity whose rows live at a data source (the platform does not insert them).
One parent's rows: [MaxRows(n, Per = Parent)]#
A read filtered to one parent — Seat.Where(s => s.Room == room) — is as big as that parent's share of the table, and
nothing in the read says how big that is. Some relations are small by their nature (a room's seats, an order's
shipping lines); many grow with time (a project's tasks, an account's events) and are a growing table read whole.
Say which, on the child:
entity Room {
[MaxLength(40)] string Name;
}
[MaxRows(40, Per = Room)]
entity Seat {
[Required] Room Room;
[MaxLength(10)] string Label;
}
List<Seat> SeatsOf(Room room) => Seat.Where(s => s.Room == room).OrderBy(s => s.Label).ToList();- The bound is enforced per parent. The insert of a 41st seat under one room is refused (
one 'Room' holds at most 40 'Seat' rows), and so is an update that moves a seat into a full room; another room is counted on its own. Writers racing for a room's last seat cannot both get it: each takes a lock for that room and counts under it. Per =names the child's reference to the parent. A pin through two declared hops (x.Seat.Room == room) is bounded too — at most the two bounds multiplied.- One parent's rows of a relation that grows are not bounded by the filter:
READ_HAS_NO_BOUNDreports the read, naming the relation, with both fixes — declare its bound when it has one, or page it:.OrderBy(…).Take(50)in a function,Rows(query)or aTakeon a page.
See also#
- Sum / Average / Min / Max / Count —
Count,Sum,Average,Min,Max, in the database - GroupBy (and HAVING) — a real
GROUP BY: one row per key with its aggregates - Cost.Mark() and Cost.StatementsSince(mark) —
Cost.Mark()/Cost.RowsReadSince(mark): hold a line on what a page reads, in a test