Fastest way to handle apply to each

(0) Share

Report

Posted on by michalparal222

Hi Power community,

I have question regarding the fastest way how to proceed with following scenario.

I have 5 SharePoint lists, one of them contains basic information about employees, and the rest some additional information - related records, lookup columns etc.. There is about 650 employees and I need to gather records valid for them, that means 650 times to go thru apply to each loop.

The actions inside apply to each:

So as you can see only data operations, no API calling (like creating records in SharePoint etc..)
When I run this flow with only 100 employees (records in SharePoint list) it takes about 20 seconds, but when I try to run it for all 650 records, it takes 5-10 hours, I mean that is crazy 😄

What should the best approach? Divide the actions into separate parallel loops? Use concurrency inside the loop?
I have tried some of mentioned approaches, like parallel loops, concurrency, combinations of those two, but it still takes hours, I would like to do it in minutes.
Thank you for your advices 🙂

Categories:

General topics

I have the same question (0)

All responses (12)

Answers (1)

leo85 609 on at

Like (0)

Report

Hi @michalparal222,
I see two potential optimizations.
First, put all the select statements into one statement.
Second, activate concurrency under settings of the action. This will open multiple threads, so that multiple records are worked on in parallel. With 650 employees, setting it to 20 should be a good option.
Regards

------------------------------
If my answer solved your issue, please mark it as a solution.
If what I wrote helped you, please give it a thumbs up.

Was this reply helpful? Yes No
michalparal222 86 on at

Like (0)

Report

Hi @leo85 ,

I have tried concurrency with 10-50 set, but as I mentioned with 100 records it took appr 20 seconds, but with 650 records it takes hours.
The Select actions are from different filter array actions, so those cannot be combined. The Last compose action is using union to combine the outputs from the Selects.

Was this reply helpful? Yes No
leo85 609 on at

Like (0)

Report

With the concurrency set to 50 you will lose quite some time to the setup of all of the threats. Try to fine tune the number of concurrencies.
You may be running into connector limitations, if your concurrency number is high. With 650 records it also depends on your license if you run into API call limitations.

Most likely there are some loops which fail and drag up the time. You could use process advisor to analyse the flow. It'll give you information on which actions take a lot of time.

Was this reply helpful? Yes No
Chriddle 8,443 Super User 2025 Season 2 on at

Like (1)

Report

I'm wondering if you can do this without even using an Apply to each at all.

If you show more details and example JSONS, I could take a look.

Was this reply helpful? Yes No
michalparal222 86 on at

Like (0)

Report

I know I should not use the concurrency set to 50. When I wrote a tried 10-50 a meant a I've tried 10,15,20,25, etc.. almost every time with same result. Check this:

650 records, concurrency to set to 20, 3,5 hour
100 records, concurrency set to 20, 14 seconds:

with concurrency off, I stopped it after 10,5 hours

As you can see no loop have failed. Every actions inside every loop shows duration 0s, sometimes 1s. These data operations do not use any connector, at least I think so. Of course something is processing these data on background, but I have not found any limits about that.

Was this reply helpful? Yes No
leo85 609 on at

Like (0)

Report

Even if they don't fail, there may be issues that take some in one of the loops.
Instead of having one loop with 650 items, you can try to have 6 or 7 parallel loops with only around 100 items. To do this, you can sort the result from the SharePoint action by a specific column and then filter it, to have around 100 items each.

Was this reply helpful? Yes No
michalparal222 86 on at

Like (0)

Report
Well @Chriddle of course I would love that, but I did not found such way. It is a pretty complex solution it may take you a while before you will crack it 🙂

Lists:
Employees
Every employee have some position
Obligation
Only list with Obligation names, used for lookups in other lists
Obligation & Position
Generates Obligation for employee to a Obligation maintenance list
Auto generated based on employee position
Obligation maintenance
All generated obligations. 1 row = Obligation assigned to an employee
Every employee can have multiple obligation (from 5 to 20) means from 5 to 20 rows
Tasks
Only list task names, used for lookups in other lists
Obligation & task
One task can have multiple obligations
Task assignment
Assign a task to an employee, one row means employee with task
This auto generates the obligations to the Obligation maintenance list
Once again one task can have multiple obligations, so this assignment can generate 5 obligations easily.
And my flow output needs to be what an employee should have, means the exact names of the obligations. So it needs to go thru all these list and:
Based on the employee position get the list of obligations
If the employee have some task assigned, then get the names of the obligations tied to this task
Then union those two obligation array into one.

There is X other flows that are maintaining the whole solution, X other lists that I did not mentioned. I have attached an excel which could help you understood it.

SharePoint lists....

Was this reply helpful? Yes No
Verified answer

Chriddle 8,443 Super User 2025 Season 2 on at

Like (0)

Report

I think a database would be much more convenient for managing all of these relationships than Sharepoint lists.

What about building a Powerapp?

However, if you want to stick with Power Automate and SP lists, I recommend doing all of these filters and queries using xpath.

I created a simplified version of this. You can find the JSON in the attachment.

Take a look and decide if you want to go down this thorny path.

flow.zip

Was this reply helpful? Yes No
michalparal222 86 on at

Like (0)

Report

Wow @Chriddle, thank you! I have learned some new skills. Thank you for your idea and sharing the json.
I have followed your example and created pretty much the same from scratch, but with more steps so I could understand it. I have one or two more questions for you.
My ultimate goal it to compare two things
Already existing obligations (items present in SharePoint list Employee Obligations maintenance) - I have this in action "Compose-FinalObligations"
with
Obligations that should be there (output of the actions you helped me to create (task obligations & position obligations) - I have this in action "Compose-FinalExistingObligations"

See my JSON attachment.

So my question is:
Now I have these two outputs what would be the best way to compare those? Specifically, if Already existing obligations for employee are the same as Obligations that should be there for employee, if not the result should be the difference.

And second question:
Action "Select-ExistingObligations" takes about 45 minutes (it is about 4000 records) I can live with that, but would there be even faster way?

BTW: I know using dataverse and power apps with relate functions would be much more better, but in this case it is from multiple reasons just not possible.

Scope.zip

Was this reply helpful? Yes No
Chriddle 8,443 Super User 2025 Season 2 on at

Like (1)

Report

Looks like the file is empty

Was this reply helpful? Yes No