Apply to Each Flow takes much longer than the sum of its steps

[JohnLove899]

Hello Everyone,

After I was noticing that one of my Flows was taking much longer than expected, I tried to optimize it to get its run time to a reasonable level. However, nothing that I have tried seems to make a difference, and the log is not providing me with any indication of why it's taking as long as it is.

The Flow is making a SQL query, and then comparing the result of that SQL Query with a SharePoint list. If a result from the SQL Query isn't on the SharePoint list, then it should create a new item. If the SQL Query result is already on the SharePoint list, then it should do nothing and move on to the next item.

When I last did a run, the SQL Query had about 25 items in it, all of which are already on the SharePoint list. The SharePoint list has about 3000 items, but I noticed that the Filter Array step is still only taking one second per item, and each iteration takes about two seconds. However, the total run time for this Flow is somehow 55 minutes, and almost all of that time is spent on the Apply to Each step (see attached screenshot).

Has anyone experienced a similar issue before? If so, how would I resolve this? I have already set the concurrency up to maximum (50), and I don't think that the Apply to Each loop contains any actions that would cause the Flow to get stuck. Strangely, this flow was running fine a couple months ago, where it would take about 40 minutes to process 600 records.

Any advice for this issue would be greatly appreciated.

[Matthy79]

Hello @JohnLove899 

First of all. The sum of the steps inside a scope, loop or whatever container is never the amount this container action needed. There is always a gap between end of action 1 and start of action 2 and so on. On some days this gab is short on other days Microsoft has troubles and you seem to wait forever. I had a day where one scope with 3 action took 23 seconds but each action only took 0 seconds. The next day everything was fine again. So let’s say this is “normal” behavior.

Now to your flow setup. You should avoid conditions inside a loop. That slows down power automate on design. Filter your items and after that only apply to each filtered item. You will see your flow will get an incredible boost.

[JohnLove899]

Hello @Matthy79 ,

Thank you for the clarification. It's unfortunate that the time between steps can be random, although it would explain why a less optimized version of the flow was only taking 40 minutes to go through 500 items a couple weeks ago. Splitting the Flow seems to have helped with that, although it would be best if it could be optimized even further.

However, I'm not sure how I could take the condition out of that loop and replace it with a Filter Array. While Filter Array will support a many to many filter if it comes from the same source (For example, column A and B are the same in an Excel list), when I try to do a many to many Filter Array with two different sources (For Example, the product ID from a SQL query result and the Product ID from a SharePoint list are the same) I get an error stating that the Filter Array cannot be evaluated because the property could not be selected, Array elements can only be selected using an integer index.

Is there another action which might let me do a many-to-many filter outside of a loop? If not, I think I could take the condition out by having Power BI do the matching and filtering, and then have the Flow run off of the Power BI result instead of using a condition inside of a loop.

[Matthy79 · Accepted solution]

As I understand you only want to check if the ids from one array are part of the other array. “contains” will work for you. You will have to create a simple array first using select in text mode holding only the ids and then you can use this array in your filter action.

[JohnLove899]

I appreciate the advice, I didn't think of using Select to turn one of the arrays into a single item, which can then be used for a "contains" filter in the Filter Array action. After some testing, I got it to work, where the SharePoint List is used for the Select, and it's used to filter the SQL query result. However, I did have to convert both the SharePoint and SQL Result IDs to strings, before the Filter Array worked correctly.

Thank you again for the answer, my flow is now running much faster than it did before. If anyone else is interested in doing this matching through a Select action, I found a useful guide for comparing two arrays here: https://pnp.github.io/blog/post/comparing-two-arrays-without-an-apply-to-each/