Optimizing Power Automate Scheduled Cloud Flow

[RishikeshRai]

Dear Community,

I have designed a Power automate flow to update a SharePoint list.

It's a scheduled cloud flow which will run once in a day and fetch data from an API, the API return around 10000 items, since the maximum number of foreach items limit for action 'Apply_to_each' is '5000', so I have split the API data using Filter Array action into multiple values based on some condition.

Now, under each 'Apply to each' for the filter array action, I check whether the current value is present in SharePoint list if yes, then a condition to check if some fields have updated value, then update the item else, create new item.

When I run the flow for the first time it worked fine, created around 3000 items in some couple of mins then the flow became slower creating 1-2 items in an hour, after canceling the flow and rerunning it again it behaves the same way.

Is there any way to optimize the flow. I have started the flow today, 20 Oct,2023 11:33 AM IST and it's been 6 hours the flow is still running. The smaller value of Filter array got completed but the value with 1000 and 4000 has been stuck in loop.

Please help me out with this issue. Attached is the Flow snapshot.

Edit:  I have also tried by enabling the Concurrency Control with its max value i.e. 50.

[EmadBeshai]

Hi @RishikeshRai ,

1- First enable the concurrency for the apply-to-each step

2- move any step inside the apply-to-each to child flow and call it inside the apply-to-each.

After applying the above steps you will see the performance difference.

If this post helps you with your problem, please mark your as Accepted solution.If you like my response, please give it a Thumbs Up.

[RishikeshRai]

Hi @EmadBeshai ,

Thanks for your reply.

Apology I forget to mention that I have also tried by enabling the Concurrency Control with its max value, still the flow performance is same.

[EmadBeshai]

Hi @RishikeshRai ,

So please do the second step by moving the logic inside the apply-to-each to new child flow and call it inside the apply-to-each.

Thank you!

[VictorIvanidze]

Try to use a single "do until" cycle instead of several "for each" ones.

[Chriddle]

I wonder if you really need to loop over all API response items.

If you search for items you need to create in advance and just loop over them, the process could be much faster, depending on the amount of items you need to create.

For testing purposes, I created a simple flow that compares two lists of 10,000 items each (200 are different) and gets the API items that are not already in the list.

It takes less than 20 seconds

APIResponses

From: range(0, 10000)

Nr: item()

Value: concat('Test-', string(mod(item(), 9800)))

ListItems

From: range(0, 10000)

Nr: item()

Value: concat('Test-', string(item()))

Filter array

From: body('ApiResponseItems')

Filter: body('ListItems') "does not contain" item()

[RishikeshRai]

Hi @Chriddle ,

Thanks for your reply!!

Indeed, the step you mentioned will be faster, but what in the case upgrading an item by comparing the metadata fields (10 custom fields) from the list to API values that has an updated value? creating an item will be rare, most likely 1-2 per month, updating the item will be frequent.

I'd would be grateful if you could assist me in optimizing these steps.

[RishikeshRai]

Hi @EmadBeshai ,

How many times can we trigger a flow? if I move the apply to each action under a new child flow, can I call the child flow 10000 times, will the child flow will be triggering this many times? does this will not hit the throttling limit of Power Automate.

Thanks,

[Chriddle]

You can also use the approach described above to identify the items to update.

So you can potentially reduce the number of loops significantly.

How many items do you need to update per day?

[EmadBeshai]

Hi @RishikeshRai ,

Maybe in case your child flow is not optimized correctly and takes a lot of time but if it completed with few seconds then no worries.

If this post helps you with your problem, please mark your as Accepted Solution.
If you like my response, please give it a Thumbs Up. 

[RishikeshRai]

Hi @Chriddle,

Thanks for your reply,

I've made changes to my Power Automate flow and decided to use a different method to update my SharePoint list. I followed the below step by @takolota to sync my SharePoint list with the Data Source and it worked perfectly, updating my list within 3 minutes.
Batch Update, Create, and Upsert SharePoint Lists - Power Platform Community (microsoft.com)

Now, this method updates all the items creating a new version in the list, I'm working on filter query to only update the items with updated values in particular columns.

[takolota]

@RishikeshRai copying my response from the other thread...

If the updated data source has some kind of modified column tracking which items have any modifications, then the simplest thing would be to filter the incoming new data to only the items modified since the last update.
Otherwise to do that while still keeping the batch processing, you would need to set the SP data read/pull to get a copy of the SharePoint list with all the relevant columns instead of just IDs & primary keys & write some more complicated advanced expression in a Filter array action to reference the correct SP item based on the primary key match & with multiple or( ) expressions check for changes in each field of the new data against the list data fields for that PK matching SP item. And even then, if you need to check many columns for changes, then that significantly increases the size of the SP data pull & risks running into the 100mb action data limit much sooner.