Answered

How to optimize a flow that compares large sets of data?

(0) Share

Report

Posted on by RaduSC

I've used the solution from this post https://powerusers.microsoft.com/t5/Building-Flows/Apply-to-each-inside-Apply-to-each-doubles-update-action/m-p/2352106 in a flow that updates a SharePoint list using Excel as input.

The flow works fine on the test data, however, when I'm running it on actual data(SP has 9000 items and Excel 2500) it takes hours, due to the fact that I have an Apply to each loop.

Considering that I have to use a condition to make sure that I'm updating the correct SP item, is there a way to make my flow faster?

Attached a screenshot of the update part of the flow:

Thank you!

Categories:

Using flows

I have the same question (0)

All responses (6)

Answers (1)

Matthy79 4,180 Super User 2024 Season 1 on at

Like (0)

Report

I don’t think you use the solution from the mentioned post. I haven’t tried it but since I provided the solution I am pretty sure there is no apply to each inside an apply to each 😉

Try to avoid conditions inside an apply to each.

Please have a deeper look at the provided solution and your flow will run much faster.

Was this reply helpful? Yes No
RaduSC 10 on at

Like (0)

Report

Indeed, the flow I have applies only 98% of the solution provided by @Matthy79.
This is because, without the condition which generates an additional Apply to each, each of the SharePoint items will be updated with each of the values from Excel and, in the end, I would obtain all the SharePoint items having the same Excel value.

Most likely, I'm missing something, but not sure what. 😞

Was this reply helpful? Yes No
Matthy79 4,180 Super User 2024 Season 1 on at

Like (0)

Report

You have to join the 2 arrays and use a filter to only get the values you want to update. So you have a filter array and than apply the loop to each item you filtered (Condition a = Condition b). But the Condition needs to be in the filter array and not inside the loop.

Was this reply helpful? Yes No
RaduSC 10 on at

Like (0)

Report

@Matthy79 I'm joining the two arrays with Select, compare the two items - "L" from Excel and "OldL" from SP - in the Filter array (this part is copied from your solution 100%, I'm only changing column names).

Then I use the apply to each to the data resulted from the filter to update the SP list.

The result is the situation from my previous message: each of the SharePoint items will be updated with each of the values from Excel and, in the end, I would obtain all the SharePoint items having the same Excel value.

Was this reply helpful? Yes No
Verified answer

Matthy79 4,180 Super User 2024 Season 1 on at

Like (3)

Report

But where is the second apply to each coming fron. If you only select values from the filtered select array, that shouldn’t happen. You apply to each sharepoint value also and that is the problem. The compare should be done using filter array and not in a condition inside the loop. If this information is missing in the array, you have to add more fields using xpath. You can add all fields at once ic you return the whole record and make a json for example.

Was this reply helpful? Yes No
RaduSC 10 on at

Like (0)

Report

@Matthy79 thank for the guidance and for the patience. I used your advice to add more fields using xpath and through this I got the SP item ID, which helped me remove the second apply to each and the condtion.

Was this reply helpful? Yes No