Microsoft Dynamics 365 | Integration, Dataverse...

High volume Dataverse dataflow times out avoid NESTED FROMs and/or JOINs best approach to resolve?

(1) Share

Report

Posted on by CU14020458-1

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?

Any guidance on which of these works best for high-volume Dataverse dataflows with heavy full-outer join chains would be much appreciated. Thanks!

Categories:

Power Platform integration

Dynamics 365 general

I have the same question (0)

All responses (2)

Answers (0)

Sort by

Suggested answer

11manish 1,079 on at

Like (1)

Report
Copy link

Link copied!
For a high-volume Gen1 Dataverse dataflow like yours, I'd prioritize the following:

Break the 11 full-outer joins into staged dataflows or intermediate tables rather than executing them as one large mashup.

Push aggregations to the source (FetchXML aggregates, SQL views, Fabric, or another data platform) whenever feasible.

Reduce the number of Full Outer joins and trim unnecessary columns before merges.

Use Table.Buffer sparingly and only after testing—it can help in specific scenarios but is not a reliable fix for million-row datasets.

Don't rely on increasing the timeout, as Gen1 Dataverse dataflows don't expose a supported configuration for this.

Given that your monitoring already shows a strong correlation between data volume and timeouts, the issue is almost certainly architectural rather than operational. Reducing the complexity of the final query plan will be much more effective than trying to extend execution limits.

Was this reply helpful? Yes No
Suggested answer

Manoj - ManoVerse 1,396 Super User 2026 Season 1 on at

Like (1)

Report
Copy link

Link copied!

Based on what you've described, I will not focus on increasing the timeout. In Gen1 Dataverse dataflows, these timeout errors are usually caused by the complexity of the generated query rather than a configurable timeout setting.
i would recommend approach to move the aggregations upstream where possible. If yearly, lifetime, and household totals can be calculated before the dataflow runs, the dataflow only needs to load pre-aggregated results instead of processing millions of rows and multiple joins during refresh.
If the dataflow must remain, I would split it into staging/computed entities and materialize intermediate results rather than performing all Full Outer Joins in a single query chain. Breaking the process into smaller stages would be a good pattern for improving refresh performance and reducing query complexity.
I would also try to reduce or eliminate the long Full Outer Join chain wherever possible. The current design forces the engine to generate a very large query, which is likely what is hitting the Dataverse TDS timeout in high-volume environments.
Table.Buffer just shifts the workload into the dataflow engine and can introduce memory pressure without resolving the underlying scalability issue so it can fix issues sometime to absorb more load but issue can come up later with when buffer will reach to its capacity.

Was this reply helpful? Yes No