Flow Long Run Time

[AndyatCompany]

Hi!

I have created two automated flows between two Sharepoint lists. One of my concerns with the automation is when large amounts of items are updated at once, causing many runs to start that bog down the system down and take a very long time to reflect the updates. Would a scheduled flow running as batch job solve this issue? Or is there a condition or expression I could use to remedy the issue? Thank you!

[Pstork1]

A scheduled flow wouldn't necessarily improve the performance of the flow itself. But you could schedule the flow to run off hours so it doesn't impact people using the lists at other time.

[AndyatCompany]

I will give that a try. Currently, my "backflow" automated flow has 30 loops running and it has been over an hour run-time and has not reflected in the list. I have tried running a concurrency rule and it has not made much of a difference other than on small updates. Are there any other options that can reduce run-times and speed up reflections? I will be developing this for my team and we will have many people running flows to that one list.

[David_MA]

I was developing a solution to create e-mails from data populated to a SharePoint list. We were getting the data out of our backend systems using an RPA process, and then synching the data to a SharePoint list on a scheduled basis using Layer2 Cloud Connector. I originally set up the flow to run when an item was created in the list. The process generates about 200 rows on the synch. Like you, it was bogging down and taking an extremely long time to complete (over an hour). 

Since the Layer2 was a scheduled process, I decided I would also schedule the Power Automate to run after the synch instead of running when an item was created. Doing it this way greatly improved the speed. Depending on the day's volume it can take between 5-15 minutes to complete versus over an hour.

The scheduled flow uses an Appy to each action, and you can set up concurrency to speed the performance:

[AndyatCompany]

This is definitely the way I need to go. Our main list is pushing 5000 items, with my list containing 450. I can update anywhere between 20 to 50 items in a day with multiple column updates. We'll have up to 7 more flows running to that one list to. Ill time the batch jobs out accordingly. Does your scheduled flow reflect just as 1 run with all your updates one it?

[David_MA]

My process actually has three flows and involves four SharePoint lists that Layer2 syncs, but my process is not the same as yours. I only mentioned my process to say that changing it from running when each item is created to a scheduled flow did improve performance for me significantly.

My flows do the following with the following schedule to give each flow plenty of time to run and is in people's in-box for the next morning:

1. The first flow does some clean-up on data. For example, it properly formats telephone numbers. This runs first, is very fast and has not taken more than a minute to run. Runs at 1800 Monday through Friday.
2. My next flow pulls the data from the four lists and combines it and formats it for the e-mail. It uses Selects, Create HTML Table actions and some compose actions to format the e-mail, which are all combined in a compose action for generating the e-mail. Runs at 1900 Monday through Friday.
   
3. In my process I also need to attach what is generated for the e-mail as a pdf attachment, so my third flow runs the next morning and deletes all of the files created from the prior day. This flow runs at 0500 Monday through Friday.

I am not retaining any of the data in my four SharePoint lists. Each day the SQL tables are only populated with that day's data and is synched at 1700. With Layer2, you set a primary key in the data, so when it syncs and doesn't see those rows in the table the next day, they are deleted from the SharePoint list automatically.

[AndyatCompany]

This is very insightful, thank you. Our main list that we pull data from is used more as a general database for our client. The lists my team will be using will be used more as workspace to allow for automatic updates to the database. I like your compose flow to scrub data, I'll incorporate that into our process as we only need certain columns from the database in our workspace. So far this is the backflow:

I have this flow starting at 12pm in our test environment and running every 4hrs. For a compose flow scheduled before the backflow, would my inputs be all the dynamic content values I want updated in the database sheet? Thank you!

[AndyatCompany]

Also would like to add the apply to each has a concurrency rule of 50.

[David_MA]

I think you are asking about my flow that cleans up data? If so, I am not really sure what you are asking, but it basically takes dynamic content from the list, manipulates it using expressions and variables to get it formatted properly, and then updates it in the original list item.

For example, in this process all of the phone numbers are in North America. Our company style guide designates that phone numbers be formatted as +1 123.456.7890, but in the data it is stored as 1234567890 or 11234567890. To do this requires these steps:

I probably could simplify this, but it is working, and the performance is fast, so I have left it as is. Is this what you are asking? If so, there are a couple other fields I clean up. At the last step then updates the original item with the cleaned-up values.

[AndyatCompany]

Coupling the scheduled flow with a concurrency rule, I got the run time down to 5 minutes. Only problem I run into now is 404 errors on every single item. Once I sort that out, this will be a complete success. Thank you for your help!

[AndyatCompany]

So I got a successful flow going for the batch job, a big issue that I'm running into now is if there are no changes to the list, the flow just continues to run until a change is made in one of the fields. Is there a way I can time out the flow so it doesn't create infinite runs? Thank you!