Fastest way to handle apply to each

[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 🙂 

[leo85]

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.

[michalparal222]

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. 

[leo85]

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.

[Chriddle]

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.

[michalparal222]

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. 

[leo85]

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. 

[michalparal222]

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:

1. Employees
   1. Every employee have some position
2. Obligation
   1. Only list with Obligation names, used for lookups in other lists
3. Obligation & Position
   1. Generates Obligation for employee to a Obligation maintenance list
   2. Auto generated based on employee position
4. Obligation maintenance
   
   1. All generated obligations. 1 row = Obligation assigned to an employee
   2. Every employee can have multiple obligation (from 5 to 20) means from 5 to 20 rows
5. Tasks
   1. Only list task names, used for lookups in other lists
6. Obligation & task
   1. One task can have multiple obligations
7. Task assignment
   1. Assign a task to an employee, one row means employee with task 
      1. This auto generates the obligations to the Obligation maintenance list
      2. 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. 

[Chriddle · Accepted solution]

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.

[michalparal222]

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. 

[Chriddle]

Looks like the file is empty

[michalparal]

Sorry, I've attached the updated one.