Scaling Power Automate, Part 2: Finding the Real Bottleneck

This is Part 2 of a three-part series on scaling Power Automate flows that process thousands of Dataverse records. Part 1 covered the batching pattern and two Do Until traps. Part 3 covers throttling, retries, and designing for partial failure.
Quick recap: I'm syncing a native Dataverse Contact Profile table (8,819 records in production) with a SQL-backed Dataverse virtual table of identity data (300,000+ rows). In Part 1, I got batching working and stopped one bad record from freezing the whole job. But the flow was still taking close to four hours to process a couple thousand records. This part is about why, and what actually fixed it.
Same labels as before: Documented is Microsoft-documented behavior, Observed is what I saw in this implementation, and Recommendation is what I'd do based on both. All timings are from my environment, not benchmarks.
Concurrency and shared variables
The obvious way to speed up a loop is to run iterations in parallel. I couldn't, at first, and the reason is worth understanding before you touch that slider.
Documented: Apply to each runs sequentially by default. With concurrency control on, you can run 1–50 iterations in parallel. Variables are global to the flow run, and Microsoft's docs warn that if you use variables inside an Apply to each and need predictable results, the loop must run sequentially. The guidance also notes that a high degree of parallelism isn't necessarily faster, and that nested Apply to each loops always run their inner loop sequentially.
The original loop used variables to hold per-contact values: the employee's email, the Admin's email, and so on. At concurrency 1 that's safe. Here's what happens at concurrency 5:
Iteration A sets varEmployeeEmail for contact A.
Iteration B sets varEmployeeEmail for contact B.
Iteration A runs its Update a row, reading varEmployeeEmail, and writes B's email onto A's record.
No error. No failed run. Just wrong data, quietly. That's the part that should scare you more than any red X in run history.
So I refactored the loop to hold no shared state at all:
Compose actions instead of Set variable. A Compose inside an Apply to each produces its output for the current iteration, so iterations don't step on each other.
item() and the current lookup's own outputs instead of copying values into variables, for example:
first(outputs('List_rows_Identity_Employee')?['body/value'])?['emailaddress']
Expressions inline where a value was only used once.
varBatchStart and varBatchSize from Part 1 stayed, and they're safe: they live outside the concurrent Apply to each, and varBatchStart only changes after the inner loop has finished.
Observed: once the loop had no shared mutable state, I raised Apply to each concurrency to 5.
Recommendation: before you touch the concurrency slider, search the loop for Set variable, Increment variable, and Append to … variable. Any one of them inside the loop is a reason to stop and refactor first. That includes "I'll just append errors to an array variable" (log them to a table instead; more on that in Part 3).
Finding the real bottleneck in run history
Observed: the original design took close to four hours to process roughly 2,000–2,500 records. My assumption was that Dataverse writes were the slow part. Run history said otherwise.
From a sample of iterations in this implementation:
Action | Typical duration (observed) |
Update a row (Dataverse, native table) | ~0.1–0.2 seconds |
List rows against the identity virtual table | ~5–14 seconds each |
That changes everything about where to spend effort. As an illustration, for 4,000 records:
Shaving 0.1 seconds off every update saves about 7 minutes in total.
One 5–14 second virtual-table lookup per record adds up to 5.5–15.5 hours of waiting if run sequentially.
Optimizing a 0.1-second write is largely irrelevant when the call before it takes 5–14 seconds.
How I measured: open a completed run, expand the Apply to each, page through a dozen or so iterations spread across different batches, and note the duration on each action. A small spreadsheet is enough. You're looking for the action that dominates, not for precision.
Documented: every action execution, including actions inside loops, retries, and extra pagination requests, counts toward your Power Platform request limits. Removing unnecessary calls helps runtime and capacity.
Recommendation: measure before you optimize. Run history is free and it's already recording every duration. Let it tell you where the time goes.
A primary-key lookup isn't automatically fast
The slow lookups surprised me, because several of them were effectively primary-key or unique-ID lookups. Those should be instant, right?
Documented: a virtual table has no rows stored in Dataverse. When a record is needed, it's retrieved at runtime from the external system through a data provider.
Observed: the traces from failed calls referenced API Management and external connector components. So each lookup traveled something like: flow → Dataverse → virtual table provider → connector → API Management → SQL, and all the way back. A well-indexed SQL query can make the SQL part fast and the round trip can still take seconds.
Some other Documented virtual-table behaviors worth knowing:
Virtual tables created with the virtual connector provider limit queries to 1,000 records.
Queries using negative filter operators (Does Not Equal, Does Not Contain) can page incorrectly beyond the first page.
For some providers, selecting specific columns isn't applied and all columns are returned anyway.
Which is also why "just load all 300,000 identity rows once and match in memory" wasn't a reasonable alternative. It's far beyond what a single query against this kind of virtual table should return, it's a huge payload to drag through a flow, and it doesn't solve the underlying problem.
Recommendations for lookups against a large virtual table:
Index on the SQL side. Index the columns you actually filter on. A GUID primary key is already indexed; if you look up by email or employee number, make sure that column is too. This is a conversation with your DBA, not a Power Automate setting.
Filter server-side. Use Filter rows so the provider and SQL do the narrowing, for example:
emailaddress eq '@{replace(items('Apply_to_each')?['emailaddress1'], '''', '''''')}'
That replace() doubles any single quote in the value. Without it, the first O'Brien in your data breaks the query, and becomes exactly the kind of "bad record" from Part 1.
Select columns. List only the columns you need. Some providers may return everything regardless, but it's cheap insurance.
Row count = 1 when you expect one match. Don't ask for a page of results to use first() on.
Push joins and enrichment upstream into SQL when the relationship lives there (next section).
Recommendation for diagnosis: time the same query directly in SQL. If SQL answers in milliseconds and the flow action takes eight seconds, the cost is in the path, not the query. More indexes won't save you there. Fewer calls will.
The N+1 problem
Here's the part that really mattered. For every contact, the original loop potentially made three virtual-table calls:
Look up the employee in the identity table.
Look up the employee's Admin (to get the Admin's email).
Look up the employee's Functional Supervisor (same reason).
Calls 2 and 3 only existed because the Admin and Supervisor email addresses weren't on the employee's identity record. This is the classic N+1 query problem: one query to get a list, then N more queries (here, up to 3N) to fill in related data one row at a time. For 8,819 contacts, that's potentially more than 26,000 calls through the slowest path in the whole flow.
SQL is built for exactly this. Resolving a relationship across hundreds of thousands of rows is one set-based join on the database server, versus thousands of individual round trips through connectors and gateways. Something like this (illustrative, not the real schema):
SELECT e.EmployeeId,
e.Email,
a.Email AS AdminEmail,
s.Email AS SupervisorEmail
FROM Identity e
LEFT JOIN Identity a ON a.EmployeeId = e.AdminId
LEFT JOIN Identity s ON s.EmployeeId = e.SupervisorId;
We removed the Admin and Functional Supervisor lookups from the loop and started enriching the SQL source so those emails come back on the employee's identity record. Whether that ends up as persisted columns or a view depends on what your virtual table provider supports and what your DBA prefers.
Observed: removing those two lookups took the smaller test population from roughly four hours to a little over one hour. In production, about 4,000 records processed in about an hour at concurrency 5.
A quick sanity check lined up with that. About 4,000 records an hour across 5 parallel lanes works out to roughly 4.5 seconds per record per lane, right in line with one virtual-table lookup being the dominant cost of each iteration. (Back-of-envelope, not a benchmark.)
Recommendation: whenever a loop does a lookup just to fetch a related value, ask whether that relationship could be resolved where the data lives. One wider row is almost always cheaper than three narrow round trips.
Up next
Faster flows and more parallelism come with a catch: the service on the other end has limits too. In Part 3, I'll cover the HTTP 429s that showed up once concurrency went up, the difference between loop concurrency and trigger concurrency (and a gotcha with the latter), retry policies, and the Try/Catch pattern that lets a multi-thousand-record sync finish even when some records don't.
Try. Fail. Learn. Repeat.




Comments