Unanswered

Slow Apply to Each loop

(0) Share

Report

Posted on by kieranbirkett95

Hello Everyone,

I need some advice on improving the performance of a flow I've created in Power Platform. This flow is designed to fetch data from a Dataverse table and parsed JSON data.

Within this flow, I'm using an "Apply to Each" loop, in which I utilize a "Filter Array" operation to determine whether the current item in the loop (which corresponds to a Unique ID from the JSON) already exists in the Dataverse table. If it does, the flow takes no action, but if it doesn't, it adds a new row.

The section of the flow that is particularly problematic, leading to extended delays, is the portion responsible for executing the "Filter Array" operation and adding new rows to the Dataverse table. These steps are noticeably impacting the overall performance of the flow.

Within the "Filter Array" operation, I've experimented with two approaches: one using a direct link to the Dataverse table, as depicted in the screenshot below, and the other involving a combination of "Select" and "Union" composes to optimize the process by reducing the need to check both tables within the loop. Unfortunately, neither of these strategies had a significant impact on the runtime of the flow.

The issue I'm facing is that the execution time of this flow varies significantly. Some runs complete in less than 10 minutes, while others take much longer, with the longest run so far lasting 26 hours, even though the data being processed remains the same.

I'm seeking advice on how to optimize the flow for consistent and faster performance or if there is a more efficient approach to achieve the same outcome. Your insights and suggestions would be greatly appreciated.

Thank you in advance!

Categories:

Building flows

I have the same question (0)

All responses (6)

Answers (1)

CFernandes 8,402 Most Valuable Professional on at

Like (0)

Report

Hey @kieranbirkett95

Can you try to set up concurrency for the apply to each loop?

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

P.S. take a look at my blog here and like & subscribe to my YouTube Channel thanks.

Was this reply helpful? Yes No
kieranbirkett95 6 on at

Like (0)

Report

I have tried this solution and set it to 50 and still resulted in the same long timescales. As I mentioned above I could be processing say 1500 things within the apply to each loop and on one 1 run it can take 10 minutes and the next run it could be 9 hours.

Was this reply helpful? Yes No
Verified answer

grantjenkins 11,063 Moderator on at

Like (0)

Report
The most efficient approach would be to filter out the values that need to be added, then just loop through those items. This will avoid the need for a Condition inside the Apply to each as you are only looping over items that need to be added.

For this example I have a Dataverse table and an Excel Table (would be the same as your CSV data since you already have it in JSON format).

Below is the Dataverse Table I'm using for this example.

Below is the Excel Table I'm using for this example. Note that it has two rows that don't exist in the Dataverse Table.

See full flow below. I'll go into each of the actions.

List rows retrieves the data from Dataverse.

List rows present in a table retrieves the data from the Excel Table.

Select extracts out the Unique IDs from the Dataverse Table into a simple array.

Filter array retrieves items from our Excel Table that are not in the Dataverse Table. Do to this, it checks to see if the list of Unique IDs from Dataverse do not contain the current Unique ID from the Excel Table.

Apply to each iterates over the items in the Filter array (only the items that don't exist in Dataverse).

Add a new row adds the rows using the data from Filter array. Below are the expressions used to get the data for Name and Unique ID.

//Name item()?['Name'] //Unique ID item()?['Unique ID']

After running my flow we would have the two additional rows added.

IN ADDITION:

I noticed that you were using a loop to extract your CSV data. We should be able to extract the CSV data into JSON format without a loop which will also help to speed up your flow. Would you be able to show what your raw CSV output is (column headers and a couple of rows of data)? Also, do any of the values contain commas with the actual text?

Was this reply helpful? Yes No
kieranbirkett95 6 on at

Like (0)

Report

Thank you is all I can say!

I just re-ran an iteration from last night that took 6 hours and it completed in under 24 seconds.

Regarding the CSV section, this only takes an average of 15 seconds inside the loop to convert the CSV into JSON format.

Certain values within the rows do contain commas within the actual text yes, I've uploaded a dummy version of the data for you 🙂

split(outputs('Get_file_content_using_path')?['body'], decodeUriComponent('%0D%0A'))

split(item(), '",')

Time and Expense....

Was this reply helpful? Yes No
grantjenkins 11,063 Moderator on at

Like (0)

Report

Glad to hear it's working and improved the duration 🙂

Given that your CSV has multiple columns that could have commas in the values, and it's only taking ~15 seconds to complete, I'd just leave it how you currently have it.

The other option would be to look at using Office Scripts to extract the data into JSON for you.

Was this reply helpful? Yes No
CU16120934-0 2 on at

Like (0)

Report

@grantjenkins Hello, i have experienced the same issue, but my apply to each step does not related to excel/ csv. can you review and share your thoughts here?

I have a flow that connects to Excel file in SharePoint, checks if it exists or not, (if yes - delete it and re-create it, if no- create it), list rows in the table, and connect to power bi paginated report (with parameter) to Send dynamic email to each email that match has found (between the excel table, to the parameter in the paginated report). as you can see, the Apply to each takes forever. it actually takes approx 5 minutes to each row and failed after 8-9 rows due to hitting the action limit. can you advise what additional options i have here (instead of this loop). many thanks!

Was this reply helpful? Yes No