cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jesenavaranjan
Helper V
Helper V

Advice on For ALL Function

Hi, I am using a ForALL function, 

to patch items from a gallery (list of questions, and 77 anwers)

the patch take a while - is there any way to speed up this patch?

As it's looping through and doing 77 updates/new statements to the DB

6 REPLIES 6
eka24
Super User
Super User

Please what is your datasource and can you copy and paste the Code.
timl
Super User
Super User

Hi @jesenavaranjan 

If it's a SQL Server DB, using a Stored Procedure and Flow will improve performance.

jesenavaranjan
Helper V
Helper V

It is  SQL Back End

- The formula is quite long and contains many parts.

But the main logic

- Collects a gallery into a collection

- Adds various fields through lookups

- Patches each row into a DB one by one

 

Jese

Jese,

When you say slow - how long is it taking?

There are probably ways to speed up the processing:

  • Add a RowId (number) column to your collection
  • Use a Concurrent function and split the collection up
  • Filter each branch (let's say 5) to divide the total # in the collection equally (using rounding logic) for each Concurrent branch
  • Then Patch in each Concurrent branch by filtering out the collection to that subset of records

Additionally, if the problem is more around "the app is slow because I don't see progress" - which is common for users.

  • Display a progress bar, maybe two overlapping shape controls (shpTotal, shpProgress)
    • Adjust the width of the progress control, based on how far the process as come along
    • See example below
  • Add a blocking mechanism of some sort, so the User cannot do other actions but is also shown that thing are progressing
ClearCollect(
    colProgress,
    {
        counterType: "Progress",
        counterCount: 0
    },
    {
        counterType: "Total",
        counterCount: CountRows(colAccounts)
    }
);
ForAll(
    colAccounts,
    UpdateIf(
        colProgress,
        counterType = "Progress",
        {counterCount: counterCount + 1}
    );
    Patch(
        Accounts,
        Defaults(Accounts),
        {name: staticAccountName}
    )
)

The example above uses CDS Account entity, and a collection based on some static data I had. The concept still applies, for deriving a total, and increment the progress on each loop of the ForAll.

Then you can see a control, such as the Width of the shpProgress shape to

shpTotal.Width * (LookUp(
    colProgress,
    counterType = "Progress",
    counterCount
) / LookUp(
    colProgress,
    counterType = "Total",
    counterCount
))

 

I tried a version of this, and it just became even slower

Because of the large volume of formulae written?

Hi Timl,

 

So I've set up a table valued stored procedure

 

How do Pass a table from power apps to this proc?

Thankyou"

Jese

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Users online (1,383)