Osy#betaRun it on Osyrin: osyrin.com

Reference / Query

Pages over large data

A page, a live read or a timer over a table that grows must never read its rows to count them. Ask the database: `Count()`, `Sum(…)`, `Any(…)` and `GroupBy(…).Select(g => new { g.Key, N = g.Count() })` are one statement each and read no rows. Page what a person looks at with `OrderBy(…).Skip(n).Take(50)`. Keep a stored roll-up, updated on write, for a figure that is expensive to work out. Every request has a READ BUDGET — 2,000 rows by default: past it `osy test` and the dev host fail the read, naming it and its line, and a deployed app warns in its own log. `ReadBudget.Raise(rows, "why")` lets one read through, with its reason beside it. A table small by its nature says so once: `[MaxRows(n)]` on the entity (n at most 2,000) refuses the insert of row n+1, and every read of it is bounded. A child that one parent holds only a few of says so with `[MaxRows(n, Per = Parent)]`; one parent's rows of a relation that grows with time are paged, never read whole.

stable6 examples compiled by CIquerydatacostperformance

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().CountTicket.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:

wherewhat happens
osy testthe test fails, naming the read as written and its file and line
the local dev hostthe request fails the same way, so you meet it while writing the page
a deployed appthe 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 requestthe 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_BOUND says 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_BOUND reports 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 a Take on a page.

See also#

Related

Sum / Average / Min / Max / Count

Fold rows down to a single number — a query or a `List<T>` you already hold. The one thing to know before you use them:…

GroupBy (and HAVING)

Group rows by a key and reduce each group to one row — `g.Key`, `g.Count()`, `g.Count(o => p)`…

Querying data

How you read data in Osy#. You write C# LINQ; it becomes one SQL statement. The rules that follow from that are the…

Cost.Mark() and Cost.StatementsSince(mark)

What a test's unit of work sent to the database between two points: take a mark, do the thing, read what it cost — in…