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 III
Super User III

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

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
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

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.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Users online (1,468)