# 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.

<!-- id: query-large-data · area: query · stability: stable · html: https://osysharp.com/reference/query/large-data/ -->

## Summary        {#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.

```osy title="the overview, asked of the database — one statement per number, no row read" test app=query-large-data
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        {#description}

### What not to write: every row, counted in memory {#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 {#paging}
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:

```osy title="fifty rows at a time, newest first" test app=query-large-data
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](https://osysharp.com/reference/testing/page-budgets/)).

### A figure that is expensive to work out: a stored roll-up {#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.

```osy title="the open count, kept on write" test app=query-large-data
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 {#no-bound}
`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 {#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.

```osy title="one read, raised with its reason" test app=query-large-data
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)]` {#max-rows}
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:

```osy title="at most 50 desks, so every read of them is bounded" test app=query-large-data
[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)]` {#max-rows-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:

```osy title="a room has at most 40 seats, so one room's seats are a bounded read" test app=query-large-data
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        {#see-also}
- [Sum / Average / Min / Max / Count](https://osysharp.com/reference/query/aggregates/) — `Count`, `Sum`, `Average`, `Min`, `Max`, in the database
- [GroupBy (and HAVING)](https://osysharp.com/reference/query/group-by/) — a real `GROUP BY`: one row per key with its aggregates
- [Cost.Mark() and Cost.StatementsSince(mark)](https://osysharp.com/reference/testing/cost/) — `Cost.Mark()` / `Cost.RowsReadSince(mark)`: hold a line on what a page reads, in a test
