Answered

Power Automate taking hours

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

I have done a flow where i have a queryresult of 13000 rows and im verifiying for each row if the 'ping' was seen more than 90 days ago and if column a and column b are empty, case 'a' is empty it copies the row to a datatableA, case 'b' , it goes to datatable b, if none is true ( ideal row ), the row is copy to datatableC, this process takes me 4 hours to run, why, how can i make it faster?

Categories:

Power Automate Desktop

I have the same question (0)

All responses (21)

Answers (2)

Sort by

Pstork1 69,543 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Two primary ways to make that kind of process run faster. One is to turn on Concurrency in the settings of the main Apply to Each loop. You can run up to 50 loops at the same time. By default it runs each loop in succession.

The second thing to do is to minimize the use of variables inside the Apply to each. Every time you access a variable inside a loop it will lock the variable and pause all the other loops that access that variable.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

im sorry if talking non-sense, but i think you are talking as if im working on web power automate, but im on desktop version, and i dont know if i can do the same or not. btw im using for each ( loop )

Was this reply helpful? Yes No
Pstork1 69,543 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Sorry, I missed that this was in the desktop forum. I don't have any specific workarounds for improving the performance of a loop on the desktop.

Was this reply helpful? Yes No
Gidi 601 on at

Like (1)

Report
Copy link

Link copied!

Hi,
can you share the actions that you are currently using for this process?
Where comes the dates from and how have you designed the loop?
(Example: if you got the query result from a database, you might think about solving the decision map in the database with e.g. views)

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

this is the flow and the attachment is the sql statement query to get the queryresult

Was this reply helpful? Yes No
mmonline 169 on at

Like (1)

Report
Copy link

Link copied!

I've not done this, so this is a theoretical.
If you can pass key elements of the datatable to the Run Javascript as an array, looping through 13,000 rows in a javascript array - even with evaluation - is fast.

In Javascript, you would create an array for each element (evaluated information) you needed to act on and return those to PowerAutomate. Then you would just act on those..

Again, I would have to play with how the data moves from PAD to Javascript and back.

I may play with that next week as I could see a use for it in the future.

Another question: The query you are returning... where is that coming from?

If it is something like SQL, can you create queries that return just the data that meets the various criteria you are checking for. It would reduce what you need to act upon.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi,
The data is coming from Excel files so i don´t know if i can check that conditions after.
I will test that javascript method if it works, thanks a lot

Was this reply helpful? Yes No
Verified answer

MichaelAnnis 5,727 Moderator on at

Like (2)

Report
Copy link

Link copied!

It’s still taking so long because you are doing one row at a time with “for each CurrentItem in query result” do something.

what if instead, you queried each set appropriately and just added the whole set (I’m paraphrasing below):

select Query where col a is empty and col b is not empty

copy result to database a

select query where col a is not empty and col b is empty

copy result to database b

select query where col a is not empty and col b is not empty

copy result to database c

good luck!

Was this reply helpful? Yes No
mmonline 169 on at

Like (1)

Report
Copy link

Link copied!

That is what I was suggesting above - though, less clearly than you did.
¯\_(ツ)_/¯

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hey, this actually worked, but the problem i have is the condition of 90 days, cause i need to compare the column lastseen to current date ( live ) and within the sql statement i cant do that, i think, this outside 90days check takes too long aswell (same problem as before)

Was this reply helpful? Yes No