Skip to main content

Query Size and Performance

Read this before writing your first query

Agility applies no default page size. A request like:

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.

#DefaultWhat it means
1No page limitOmitting page returns the entire result set. The response reports pageSize="2147483647" — Int32.MaxValue, the server's way of saying "unpaged"
2A default attribute selectionOmitting sel does not return a minimal asset. It returns GetBasicAttributeDefinitions(), a per-type basic set — real fields, on every asset returned
3Implicit relation expansionEvery 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 and Sort and paging.

What to Send Instead​

Always be explicit about how many assets and which attributes.

rest-1.v1 — Positional Paging, Size First​

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:

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​

{
  "from": "Story",
  "select": ["Name", "Number", "Status.Name"],
  "where": { "Scope.Name": "CallCenter" },
  "page": { "size": 50, "start": 0 },
  "needTotal": true
}
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.

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

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

PatternWhy it costs
Rel.Sub.AttributeEach dot is another join
Rel[filter].AttributeAn inline filter is evaluated per traversed asset
Rel.Attribute.@SumAn aggregation 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 allSilently also returns Name, ShortName and DisplayBy

That last row is the one people miss — see the three compounding defaults 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:

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

See Meta API and 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.

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:

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

Before running a query-scoped update or operation, use preview mode to see exactly which assets it will touch. See 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:

{
  "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 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 — 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, 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.
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