---
id: query-size-and-performance
title: 'Query Size and Performance'
description: 'Agility applies no default page size, so an unbounded query tries to return every matching asset. How three separate defaults compound, and the request shape that avoids them.'
sidebar_label: 'Query Size and Performance'
sidebar_position: 1
tags: ['performance', 'paging', 'queries', 'best-practices']
---

:::danger Read this before writing your first query

**Agility applies no default page size.** A request like:

```http
GET ~/rest-1.v1/Data/Story
```

does not return a reasonable first page. It attempts to return **every Story the calling
identity can see**, with a default set of attributes on each one. On a mature instance that is
tens of thousands of assets in a single response.

:::

This is the most common cause of self-inflicted performance problems in Agility integrations,
and it is easy to hit because nothing about the request looks wrong. It succeeds. It just
succeeds enormously.

## Three Defaults That Compound

Each of these is individually reasonable. Together they mean an unbounded request returns far
more than most callers expect.

| # | Default | What it means |
| --- | --- | --- |
| 1 | **No page limit** | Omitting `page` returns the entire result set. The response reports `pageSize="2147483647"` — `Int32.MaxValue`, the server's way of saying "unpaged" |
| 2 | **A default attribute selection** | Omitting `sel` does not return a minimal asset. It returns `GetBasicAttributeDefinitions()`, a per-type basic set — real fields, on every asset returned |
| 3 | **Implicit relation expansion** | Every *relation* you select additionally returns the related asset's `Name`, `ShortName` and `DisplayBy`. So `sel=Status` returns `Status`, `Status.Name` and often `Status.ShortName` |

The multiplication is what hurts: **(all matching assets) × (default attribute set) × (silent
expansion per relation)**. Adding one more relation to `sel` does not add one column, it adds up
to three — across every row in an unbounded result set.

All three are documented in detail on
[the query grammar overview](/reference/query-grammar) and
[Sort and paging](/reference/query-grammar/sort-and-paging).

## What to Send Instead

Always be explicit about **how many** assets and **which** attributes.

### `rest-1.v1` — Positional Paging, Size First

```http
GET ~/rest-1.v1/Data/Story?sel=Name,Number,Status.Name&page=50,0&needTotal=true
```

`page=<size>,<start>` — **size comes first.** This is the opposite of most APIs, and getting it
backwards fails silently: `page=0,50` is a zero-size page, not "the first 50".

Advance `start` by `size` per page, not by one:

```http
GET ~/rest-1.v1/Data/Story?sel=Name,Number&page=50,0     # assets 1-50
GET ~/rest-1.v1/Data/Story?sel=Name,Number&page=50,50    # assets 51-100
```

### `query.v1` — a Named Mapping

```json
{
  "from": "Story",
  "select": ["Name", "Number", "Status.Name"],
  "where": { "Scope.Name": "CallCenter" },
  "page": { "size": 50, "start": 0 },
  "needTotal": true
}
```

:::warning The two surfaces take paging differently

`query.v1` uses a **named mapping** — `page: {size, start}`. `rest-1.v1` uses a **positional**
query string — `page=<size>,<start>`. A query ported between the two cannot carry the paging
across unchanged.

:::

## Paging Correctly

**Sort before you page.** Paging is applied after sorting. Paging an unsorted list has no
stable order underneath it, so assets can repeat or be skipped between pages even though every
individual response looks fine.

```http
GET ~/rest-1.v1/Data/Story?sel=Name&sort=+Number&page=50,0
```

**Stop on a short page — you don't need a total.** The cheapest correct pager never asks
how many results exist: request pages until one comes back with **fewer results than the
page size** — that page is the last. (A result set that is an exact multiple of the page
size costs one extra empty-page request, which is fine.)

```text
start = 0
loop:
  results = query(page: {size: N, start})
  process(results)
  if len(results) < N: done
  start += N
```

This also skips the cost of computing the total — a real query of its own on every page.

**Reach for `needTotal=true` only when you genuinely need the count** (a progress bar, a
report header). Two traps when you do:

- `total` is only computed on a paged query when requested; otherwise the
  paged response omits it — "not computed" looks like a missing attribute, not
  a count of zero.
- Whether it is computed *by default* comes from the per-instance `ApiDefaultNeedTotal`
  setting, so an integration that pages off `total` can work against one instance and break
  against another with no code change. Always send `needTotal=true` when you rely on the
  count, and treat a missing `total` as "unknown" rather than as a number.

## Finding Which Projection Is Expensive

"Select less" is easy advice and hard to act on when a query has twenty entries
in `select` and you do not know which one is costing you. A **leave-one-out**
measurement answers that directly, and needs nothing but a clock.

The method:

1. Time the full query several times and average it. This is your baseline.
2. For each entry in `select`, remove **only that entry** and time the query again.
3. The difference between the baseline and each reduced run is that projection's
   marginal cost.

```js
const baseCost = await runTrials(query, trials);

for (const projection of query.select) {
  const reduced = {
    ...query,
    select: query.select.filter((p) => p !== projection),
  };
  const costWithout = await runTrials(reduced, trials);
  console.log(projection, 'costs', baseCost - costWithout, 'seconds');
}
```

Two details matter for the numbers to mean anything:

- **Discard a warm-up run** before timing. The first execution pays for plan
  compilation and a cold cache, and will dominate a small sample.
- **Average several trials.** Individual runs on a shared instance vary enough to
  invert the ranking of two similar projections.

This measures *marginal* cost, not total. Two projections that traverse the same
relation each look cheap on their own, because removing one still leaves the join
in place for the other. If nothing looks expensive but the query is slow, remove a
whole group of related paths at once rather than one at a time.

### What Tends to Be Expensive

The projections worth measuring first, from the shape of the syntax rather than
from any benchmark:

| Pattern | Why it costs |
| --- | --- |
| `Rel.Sub.Attribute` | Each dot is another join |
| `Rel[filter].Attribute` | An inline filter is evaluated per traversed asset |
| `Rel.Attribute.@Sum` | An [aggregation](/reference/query-grammar/attribute-paths) rolls up a whole set per row |
| `SubsMeAndDown:Type[...]` | A recursive relation, downcast, then filtered |
| `History[...]` | History multiplies rows by version count |
| Any relation at all | Silently also returns `Name`, `ShortName` and `DisplayBy` |

That last row is the one people miss — see
[the three compounding defaults](#three-defaults-that-compound) above.

## Select Only What You Need

Naming attributes explicitly is the other half of the fix, and often the larger one — it
reduces the width of every row rather than the number of rows.

- **Name every attribute you actually use.** The default set is a convenience for exploration,
  not a payload you want in production.
- **Watch relation count.** Each relation in `sel` costs up to three returned attributes, not
  one.
- **Custom fields are never in the default set** — they must be selected explicitly regardless.

Discover what is available for a type from `meta.v1` rather than guessing:

```http
GET ~/meta.v1/Story?xsl=api.xsl
```

See [Meta API](/apis/meta-v1) and
[Attribute paths](/reference/query-grammar/attribute-paths).

## Single-Asset Requests Ignore `page`

On a single-asset `Data` URL such as `/Data/Story/1234`, the `where`, `sort`, `page` and `find`
parameters are **accepted and silently dropped**. There is nothing to page, so this is harmless
here — but it is the same silent-drop behaviour that causes trouble elsewhere, and it does not
apply to `/Hist/...` URLs, where all four take effect.

See [the query grammar overview](/reference/query-grammar).

## Bulk Operations Have No Implicit Limit Either

The same principle applies to `~/api/asset`. A query-scoped update with no `where` matches
every asset of that type the caller can see:

```json
{ "from": "Story", "update": { "Description": "..." } }
```

Before running a query-scoped update or operation, use
[preview mode](/apis/asset-bulk/preview-mode) to see exactly which assets it will touch.
See [Updating assets](/apis/asset-bulk/updating-assets).

## Extracting at Scale: Full Loads and Delta Sync

Feeding a warehouse or data hub from Agility is a common integration, and the
same field-proven rules apply whichever surface you use — `query.v1`,
`api/asset` or `rest-1.v1` all page, all emit JSON, and all can include or
exclude deleted assets. The endpoint choice matters far less than how you
query it:

- **Project the absolute minimum.** Every attribute you select costs
  something, every relation costs a join — and you should know what each
  projected attribute means before you ship it downstream.
- **Filter the result set to the absolute minimum** — scope, team, release.
  Do not extract the instance and filter in the warehouse.
- **Filter on OIDs, not names.** Names are localized, editable and
  duplicable; OIDs are stable.
- **Page without `needTotal`.** Computing the total costs an extra query on
  every page and you do not need it: keep requesting pages until one comes
  back with fewer results than the page size — that one is the last.
- **Prefer fewer, larger pages.** Per-request overhead dominates at small
  page sizes; page sizes in the hundreds to low thousands have worked well
  in production extractions. Very large pages combined with wide
  projections can time out instead — if a request hangs, shrink the page or
  the projection, not both directions at once.
- **Use the UTC date attributes** — filter and project `ChangeDateUTC` /
  `CreateDateUTC`, not their local-time counterparts, to stay immune to
  timezone and DST weirdness.
- **Pin one upper time bound for the whole run.** Data changes while you
  page. Filtering every query in the run with the same fixed
  `ChangeDateUTC < run-start` bound keeps the extract consistent instead of
  torn across pages.

**Delta sync** follows directly: an incremental load is just "everything
changed since the previous run". Record each run's fixed upper bound, and
next time filter `ChangeDateUTC` from that value forward:

```json
{
  "from": "Story",
  "filter": ["ChangeDateUTC>='2026-06-01T00:00:00'", "ChangeDateUTC<'2026-07-01T00:00:00'"],
  "select": ["Number", "Name", "ChangeDateUTC"],
  "page": { "size": 500, "start": 0 }
}
```

Two operational notes:

- **On-premise instances** may need offset-fetch paging and (when projecting
  many custom attributes or relations) query-optimization settings enabled
  in the instance configuration — that is an administrator task; raise it
  with Digital.ai Support.
- For continuous heavy analytics, ask whether the
  [Agility Analytics](https://docs.digital.ai/agility/) data mart fits
  better than repeated API extraction — extraction re-derives what the data
  mart already maintains.

Two clarifications that save confusion:

- **"Bulk API" naming.** Paginated `query.v1` extraction is sometimes
  informally called "the bulk API". On this site, **Bulk API always means
  [`api/asset`](/apis/asset-bulk)** — the surface whose requests batch
  queries, creates, updates and operations.
- **Extracting several types per round trip.** An `api/asset` request can
  carry [multiple query commands](/apis/asset-bulk/queries), so one call can
  fetch a page of Epics, Stories and Defects together. Each query still
  pages independently — batching reduces round trips, not result sizes.
  One caution: the server's SQL timeout applies **per query, not per
  request**, so a request stuffed with several large queries can legally run
  far longer than any single query could. Keep batched queries modest, and
  set your client's timeout with the whole request in mind.

:::warning Slow queries are everyone's problem
A query that takes long enough to brush against the SQL timeout is placing
heavy load on the same database that serves the interactive product —
concurrent Agility users feel your extraction as slow boards and grids.
Treat long-running queries as a signal to narrow the projection, tighten the
filter or shrink the page, not as a budget to spend.
:::

## Checklist

Before shipping an integration:

- [ ] Every list query sends an explicit `page`
- [ ] Every list query sends an explicit `sel` / `select`
- [ ] Every paged query sends a `sort`, so paging is stable
- [ ] `needTotal=true` is sent wherever `total` is read, and its absence is handled
- [ ] Relation selections are counted with expansion in mind
- [ ] Query-scoped bulk updates were checked in preview mode first

## Related

- [Sort and paging](/reference/query-grammar/sort-and-paging) — the normative reference
- [Query grammar overview](/reference/query-grammar) — selection defaults and expansion
- [Performance](/guides/performance) — caching and broader optimisation
- [Choosing an API](/getting-started/choosing-an-api)