How does flow cope when a lot of actions are excecuted?

As great as Power Automate cloud flow is, it has its own challenges. For example, in a situation where a lot of rows are processed and/or operations are performed (by flow’s standards) quickly.

Let us get to the bottom of these problems this time. We’ll look at a flow I have presented in my personal blog earlier. This flow sends feedback requests from employees to the people they mainly work with.

The solution works well in a small organization (50 people), but what happens when the organization has 2,500 employees?

Run a loop side by side (max. 50)

The first bottleneck with this flow is in the action of sending an adaptive card. In the flow logic we need to wait for an answer to the post adaptive card and wait for a response action. The flow instance will not continue processing further business logic until a response has been given by the user.

By default, the flow sends one feedback request (=adaptive card) and waits for a response. The next feedback request will not be sent until the previous one has been responded to. If the user does not respond to the query, the process stops there.

Great.

You can fix this by setting the degree of parallelism of the outer loop (Apply to each) to full (50). In the inner loop, a value of 4 can be used (no more feedback requests are sent for one employee).

Now there will be 50 feedback requests at the same time. The solution therefore works in a 50-person organization. But not much bigger. In a 1,000-person organization, we are quickly in a situation where 50 people are not responding to these feedback requests. And that’s where the whole process stops.

Child flow – Collect feedback from one person

The problem can be solved via a child flow. They’re handy because by using them you can:

• divide a complex flow into several separate flows that call each other
• trigger the same flow from several different flows

Logic is obviously easier to maintain through child flows. However, they can also be used to increase the number of parallel runs. That is what we are trying to achieve today.

Our child flow handles the collection of feedback from one person. This flow:

• receives a person’s identifier as a parameter
• finds out who to send a feedback requests to
• sends feedback requests
• saves replies, if any.

The flow looks like this:

To use any flow as a child flow, it must return a response to the caller. Since we don’t want to wait until the user gives the answer, we’ll return the response immediately on the parallel branch:

Actions in the child flow should always be performed with preconfigured connections, not with the initiator’s connection. This is defined in run-only users:

Next, we need to build a new flow that will call this child flow.

Main workflow

There’s not much left to do in the actual flow. It searches for the people for whom feedback is requested and starts a child flow for each.

You can start an unlimited number of child flows (as long as you do not limit the parallelity of its launches).

Bottlenecks to consider

Now we finally get to the really interesting question. By flow’s standards, we’re already processing a reasonable set of feedback items (2,500 – total count of employees). What should we take into account here?

Paging

Feedback items are retrieved from an Excel file. They could just as easily be retrieved using Office 365 Users, AAD/M365 group, SharePoint list, Dataverse, etc.

Actions that return rows have the return set paging turned off by default. “Get rows from a SharePoint list” returns only 100 lines. 256 rows will be returned from an Excel table, etc.

If there are more lines, you must first turn on Pagination in the function settings and specify the maximum number of lines to process (Threshold).

With a Microsoft 365 license, you can set the number of items to up to 5,000. Our 2,500 items could therefore be processed with the standard licenses included in Microsoft 365 / Office 365 plans.

Connection throttling

Our main flow initiates the child flow at around 1-2 times per second:

We will quickly discover that some of the child flows end up in an error state:

This is caused by the Microsoft Teams action used in our child flow:

The request failed. Response content: '{"statusCode":403,"message":"Out of call volume quota. Quota will be replenished in 00:40:25."}'.

What’s this error message all about?

Each connector has its own limitations. With Teams, you can make up to 100 API calls per minute. You also are limited to a maximum of 300 invitations per hour. Right now we are doing 1,800+ per hour…

Our child flow performs up to 4 Teams operations. So, we can launch a maximum of 75 child flow per hour. Round down safely and let’s start one per minute.

We can do this by adding a one minute delay to the main flow after the child flow is started. Note that the loop is not run side by side, but one at a time (default setting). Luckily we don’t need any more speed to run our loop.

This added delay allows the flow to proceed slowly enough and the limits of the connectors are not exceeded.

Yes, you counted right. It now takes more than 40 hours to go through 2,500 people.

Increasing the delay also consumes 2,500 additional Power Platform requests in a 2,500 employee organization.

Power Platform Requests

Our flow is now going to reach the finish line successfully. But what does this look like from a Power Platform request consumption perspective?

One child flow execution creates 15 requests. A total of 2,500 runs are performed and those generate 37,500 requests. 35,000 of these will be run immediately. 2,500 are scattered over a longer period of time, as they are executed when users respond to the feedback requests they’ve received.

The main workflow consumes 7,502 requests.

In all, 45002 requests are generated.

What kind of a license will it require to run such a solution?

• An Office 365 user account can execute 6,000 requests per 24h. Not enough for us.
• With a Power Automate per user license, you can complete 40,000 requests per 24h. This is enough, as the flow takes ~40h to complete, which means that requests are also spread over two days. There will be hardly any other requests that can be made with the same user ID on those days, though.
• With a Power Automate per flow license, you can complete 250,000 requests per 24h. No need to worry about hitting that limit in our example scenario.

Of course, sending feedback requests can also be spread over several days. For example, feedback requests for different business units could be sent on different days.

Summary

What did we learn from this scenario? When using Power Automate cloud flow to handle thousands of rows worth of data, new and confusing challenges may arise.

From the developer’s point of view, flow performance is often painfully slow. Yet at the same time, however, it is so fast that it easily exceeds the restrictions associated with the use of connections.

What’s important to note is that these problems don’t come up when you test the flow with just a little bit of test data.

Here are three questions you should always think about when creating new Power Automate cloud flows:

• How long will it take for the flow to run when using real data volumes?
• How much and often will the flow call connector functions (check the limitations of the function)?
• How many Power Platform requests is the flow expected to consume?