Slow Canvas App on a Two-Million-Row Table

Delete the OnStart collection; push every filter to the server.

• The collection is the whole problem: it is a full-region download performed at startup, it truncates silently at the data row limit, and every subsequent count is a lie. Remove it and let the gallery query Dataverse directly.
• Delegable filters on Dataverse include =, <, >, &&, and StartsWith against indexed text. Build the gallery on Filter(Requests, Region = varRegion, Status = varStatus) with a date bound, and add a With/Sort chain that stays delegable.
• Contains on a free-text field is the part that will not delegate. Convert the search to StartsWith on an indexed customer-name column, or add a dedicated indexed search column written by a plugin, rather than accepting a partial result set that silently drops rows.
• Add DelayOutput on the search text input so keystrokes do not each fire a query, and set the gallery to load a page at a time rather than the full row limit.
• Move independent calls into Concurrent() and replace OnStart constants with named formulas so they are not recalculated per control.

Verify: turn on the delegation warning pane and clear it, then test in a browser throttled to slow 3G with the row count visible.

Aggregate on the server; do not count in the client.

• Client-side CountRows(Filter(...)) over a filtered set either delegates unreliably or walks a truncated collection - the exact failure the team already has. Any approach that counts rows in the app will drift.
• Options, in order of preference:
  1. Rollup or calculated columns on the parent (for example the account) maintained by the platform, read as single values. Fixed definition, no per-view cost.
  2. A Dataverse view grouped on status, surfaced through a small model-driven chart or a FetchXML aggregate executed by a flow, writing results to a summary table the canvas app reads in one row. Refresh on a timer or on screen OnVisible.
  3. Web API RetrieveMultiple with aggregation called from a custom connector or flow - more moving parts, justified only if the grouping must be dynamic.
• "Accurate to the minute" is a service-level question: agree the refresh interval (for example 60 seconds while the screen is visible) and make the last-refreshed timestamp visible so users do not trust stale numbers.

Verify: compare the tile against a Dataverse advanced find count at three points during a busy period, and confirm the app still renders when the summary call fails.

Stay on Dataverse; add a read-only reporting path, not a second source of truth.

• Dataverse handles 2.3M rows comfortably with indexed columns and delegable filters; the current slowness is a query design problem that would follow the data to SQL.
• Moving to Azure SQL introduces a second security model, a second set of connectors, and a synchronisation problem between the two stores - and canvas delegation against SQL is not more generous than against Dataverse, so the Contains search does not get easier.
• For analytics, use the Dataverse TDS endpoint (read-only, intended for reporting tools such as Power BI or SSMS queries) or Synapse Link, rather than writing to SQL from the app. Do not attempt to write through the TDS endpoint.
• If a future requirement genuinely needs sub-second joins across external operational data, that is a virtual table or a dataflow-backed table decision, scoped to that requirement - not a wholesale migration.

Verify: re-measure gallery load time after the delegation fixes; if the single-table query is still slow at 2.3M rows, the answer is indexing and narrower filters, not a platform change.