Dynamics Community Forum Thread Details

Background

We have a Gen1 Dataverse dataflow that calculates aggregate totals per record (yearly, prior-year, lifetime, and household rollups) and writes them to two output entities, Merge Account and Merge Contact. Each output entity is built from a chain of ~11 Table.NestedJoin (JoinKind.FullOuter) joins over ~12 upstream aggregate queries.

The problem

The dataflow refreshes without issue on smaller environments but times out on high-volume ones with:

Microsoft SQL: "The updated two minutes (2) timeout period elapsed prior to completion of the operation. Please avoid using SELECT *, or NESTED FROMs and/or JOINs to execute the query with default five minutes (5) timeout." ErrorCode = 10478; Number = 40000

(Some environments show error 40197 instead.) It stops on the Merge step.

What we've implemented so far

We've built a monitoring solution: a cloud flow that detects dataflow refresh failures and logs the details (dataflow name, ID, and error message) into a custom Refresh Audit table, surfaced in a model-driven app so the team has visibility into failures.

We've analyzed the failure across multiple environments and confirmed it correlates with data volume — the same dataflow completes on smaller datasets and times out on larger ones, with the filtered row volume and per-record density appearing to be the main drivers.

We've identified that the full-outer joins don't appear to fold, so a large nested query is sent to the Dataverse TDS endpoint and hits the query timeout — i.e. it's a timeout, not a data/row error.

Approaches we're evaluating — would these be effective?

Increasing the query/command timeout for a Gen1 Dataverse dataflow — is that configurable, and where?

Using Table.Buffer on the join inputs to force local evaluation and avoid pushing the nested query to the TDS endpoint — would this reliably help, or just shift the bottleneck?

Staging the upstream queries into computed/staged entities (or a separate staging dataflow) — is this the recommended pattern, and does it need enhanced compute / premium?

For very large datasets (1M+ rows), pushing the aggregation source-side (FetchXML aggregate / SQL view) — is this the more reliable long-term approach?

Is there a supported way to get per-step timing / query diagnostics for a dataflow refresh, to pinpoint the heaviest step and enrich our monitoring?