Power Apps Performance Optimization Guidelines

Power Apps Performance Optimization Guidelines
Table Of Contents:
• Load Multiple Datasets ConcurrentlyWrite Formulas That Use DelegationCache Data In Collections And VariablesLimit The Size Of Collections“Batch Patch” Multiple Changes To A Datasource Table At OnceReduce Code In The App's OnStart PropertyMinimize Number Of Controls On A Single ScreenEnable DelayOutput For Text Input ControlsDo Not Reference Controls On Other ScreensEliminate The N+1 Problem




Load Multiple Datasets Concurrently

Making connector calls sequentially is slow because the current connector call must be completed before the next one starts.  The Concurrent function allows Power Apps to load data faster by simultaneously processing multiple connector calls at once.  Only use the Concurrent function to retrieve data stored in cloud.  There is no advantage to using concurrent when working with data already on the device (i.e. variables and collections).

// Sequential code execution (slower)
Set(
    gblUserProfile,
    Office365Users.GetUserProfileV2(User().Email)
);
ClearCollect(
    colActiveProjects,
    Filter(
        Projects,
        ProjectStatus.Value="Active"
    )
)

// Simultaneous code execution (faster)
Concurrent(
    // Thread #1
    Set(
        gblUserProfile,
        Office365Users.GetUserProfileV2(User().Email)
    ),
    // Thread #2
    ClearCollect(
        colActiveProjects,
        Filter(
            Projects,
            ProjectStatus.Value="Active"
        )
    )
)




Write Formulas That Use Delegation

Always write formulas that can be delegated to the cloud datasource.  Delegation is when data operations such as filter, lookup and search are performed in the cloud (i.e. SharePoint, Dataverse) instead of on the user’s device.  Data operations can be performed faster in the cloud because there are more computing resources than a laptop or mobile phone.  Also, less data will be transmitted to the user’s device because it has already been filtered by the datasource.

Refer to the official Power Apps documentation to determine which Power Fx functions can be delegated.  The supported functions are different for SharePoint, Dataverse & SQL.  A warning will appear in the app checker when a function cannot be delegated.



Dataverse views are not subject to delegation rules.  Use Dataverse views to write filter criteria that cannot be delegated using Power Apps formulas.

Filter(
    'Device Orders',
    'Device Orders (Views)'.'Active Device Orders'
)




Cache Data In Collections And Variables

Store frequently used data in collections and variables.  Data stored in memory can be accessed very quickly.  A cloud datasrouce must receive a connector call, perform a query and send a response back to the device before data can be displayed on-screen.

// Store the currency exchange rates table in memory for quicker access
ClearCollect(
    colCurrencyExchangeRates,
    'Currency Exchange Rates',
)




Limit The Size Of Collections

Limit the size of collections to the least number of rows and columns that required by the app. Mobile devices have tight restrictions on memory usage. Collections are stored in the device’s memory. If too much memory is in use the mobile operating system will kill the Power Apps process and the app will crash.

Use the ShowColumns function to select only specific columns and drop the rest from the collection. Enable explict column selection to fetch only table columns used in the app when connecting to Dataverse.

// Selecting only desired columns from the accounts table
ClearCollect(
    colAccounts
    ShowColumns(
        Accounts,
        "name",
       "city",
        "state",
         "zipcode"
    )
)




“Batch Patch” Multiple Changes To A Datasource Table At Once

Quickly update multiple records in the same datasource table by using the “batch patch” technique.  “Batch patch” enables record updates to be made simultaneously.  The traditional ForAll + Patch method is slower because it makes the updates sequentially.

// Collection of records to update
ClearCollect(
    colUpdateEmployees,
    Table(
        {ID: 2, FullName: "Alice Henderson", Active: true},
        {ID: 4, FullName: "David Wright", Active: false},
        {ID: 5, FullName: "Mary Allen", Active: false}
    )
);
// Update records one-by-one (slower)
ForAll(
    colUpdateEmployees,
    Patch(
        Employees,
        LookUp(Employees, ID=colUpdateEmployees[@ID]),
        {
            FullName: colUpdateEmployees[@FullName],
            Active: colUpdateEmployees[@Active]
        }
    )
);
// Bulk update multiple records at once (faster)
Patch(
    Employees,
    ShowColumns(
        colUpdateEmployees,
        "ID",
        "FullName",
        "Active"
    )
);




Reduce Code In The App’s OnStart Property

The more code that is in the app’s OnStart property, the longer an app will take to start. Improve app startup time by initializing global variables in the OnVisible property of the app’s first screen. If possible, further defer setting variables until the screen they are needed.

Time to first screen metrics can be found in the app’s Analytics page.  Go to the maker portal, click on the three dots beside the app, select Analytics (preview), then choose Performance.




Minimize Number Of Controls On A Single Screen

Every control added to a screen increases memory usage when the screen loads. Try to achieve a screen design with the fewest controls possible.  A screen with fewer controls on it is faster to render and consumes less memory.  For screens with too many controls, consider dividing their functionality across multiple screens.

Use a gallery to display repetitive controls. Each control in a gallery only counts as 1 control no matter how many times it is shown.




Enable DelayOutput For Text Input Controls

The Text property of a text input is updated after each keystroke.  Set the DelayOutput property of the input to true to wait until after the user stops typing.  This is useful when building a search bar connected to a gallery.  With DelayOutput enabled the app will only make one request to the datasource when typing stops, as opposed to each keystroke.




Do Not Reference Controls On Other Screens

When writing formulas, only reference controls on the current screen. Do not reference controls on other screens. It will force Power Apps to keep that other screen in memory even though it is not being displayed on the device. Use a global variable to store the values found on other screens and refer to the variable instead. 




Eliminate The N+1 Problem

The N+1 problem is caused when an app must make N+1 connector calls, where N is the number of items.  For example, let’s say we want to display a list of business Contacts in a gallery.  The Items property requires 1 connector call to get Contacts from the datasource.

// ITEMS property of a gallery
Contacts



Each Contact has a related Account (i.e. an organization).  To display the Account Name we insert a label into the gallery with this  code in the text property.  As a result one additional connector call must be made for each row in the gallery.  If there are 100 rows in the gallery, there will be 101 total connector calls total (1 gallery +100 rows).

// TEXT property of the account name label
LookUp(Accounts, ID=ThisItem.AccountID, 'Account Name')


The solution to the N+1 problem for Dataverse is quite simple.  Dataverse automatically fetches the required data in related tables during the connector call for Contacts. 

// TEXT property of the account name label
ThisItem.Account.'Account Name'



SharePoint lists are not a relational database and cannot return all related data in one connector call.  We cannot eliminate N+1 but we can reduce the number of connector calls to SharePoint.  Collect all data in the Accounts and Contacts prior to opening the gallery screen.  Then add a new column called “Account Name” to the Contacts table by joining it with the Accounts table.  Display the resulting collection in the items property of the gallery.

// Download all contacts and accounts prior to entering the gallery screen
ClearCollect(colAccounts, Accounts);
ClearCollect(colContacts, Contacts);


// Join tables to get account name
ClearCollect(
    colGalleryData,
    AddColumns(
        colContacts,
        "AccountName",
        LookUp(colAccounts, ID=ThisItem.AccountID, 'Account Name')
    )
);

// new ITEMS property of the gallery
colGalleryData





Questions?

If you have any questions about Power Apps Performance Optimization Guidelines please leave a message in the comments section below. You can post using your email address and are not required to create an account to join the discussion.

Subscribe
Notify of
guest

29 Comments
Oldest
Newest
Inline Feedbacks
View all comments
Piotr Rusak
Piotr Rusak
1 year ago

Hi Matthew – great article (as always). I spotted 1 mistake – in last example – in Lookup(Accounts … should be Lookup(colAccounts… (just to really avoid N+1 calls ;-))
Thank you for your contribution to the community – Matthew You Rock !!

Gil Shalem
Gil Shalem
1 year ago

Hi Matthew, and thanks for the great content!
About the very last code snippet:

// Join tables to get account name
ClearCollect(
colGalleryData,
AddColumns(
colContacts,
“AccountName”,
LookUp(Accounts, ID=ThisItem.AccountID, ‘Account Name’)
)
);
If we already captured the Account table into a collection, why do we need to lookup the account name in the Account DataSource? Wouldn’t it be better to now look it up in the colAccounts collection? meaning:
LookUp(colAccounts, ID=ThisItem.AccountID, ‘Account Name’)

Thanks,
Gil.

Haylee
Haylee
1 year ago

I just want to send a huge THANK YOU for all of these helpful guides you create and share with the community!

Voltron
Voltron
1 year ago
Reply to  Haylee

Присоединяюсь!

Voltron
Voltron
1 year ago

in Russian I join means that I share, I support. applies to this comment with gratitude. your blog is one of the most helpful. Thanks

Last edited 1 year ago by Voltron
Jay
Jay
1 year ago

Hi Matthew, thanks for the very informative article.

May i just if you collect, you will just get the first 2000 records from the SharePoint, right? And also, is it okay to put my OnStart code to the OnVisible property of my first screen? I also have navigate function on my OnStart code.

Many thanks, Mate!

Jay
Jay
1 year ago

Hi Matt,
I have deep linking function and I just included it on my OnVisible property.

Larry
Larry
1 year ago

Hi Matt,
Thanks for developing this helpful website. I wanted to ask you and community how they’d design an app that has over 100K records and growing. With 2K delegation limitations, I’ve tried the collection approach and kept only the required columns/fields but it can still take up to 4-5 minutes to load all of the data. How would you improve on the loading time or is there other better approaches?

Larry
Larry
1 year ago

To ensure that all of the data are there and to prevent delegation warnings that omit a bulk of the data, I found that using this approach retrieves the correct data. Even filtering a subset of the data, it runs into delegation warnings because there are so many records. I know there is probably a better approach. I’m open to ideas to try.

beyondthebox
beyondthebox
10 months ago
Reply to  Larry

Power Apps makes no secret of their data row limitations. If you are insistent that you have to bring in 100,000 rows…this is not the product for your use case.

BTW, I believe that 2000 rows it just too low and that this is a Microsoft centric limitation. Regardless, I have actually found few reasons that much data is needed in an app. Larry, your provided explanation does not really tell us why you need that much data at one time.

Eric Kaplan
1 year ago

Matthew, Great content. You are an inspiration to this long time Excel User learning Power Apps. I have followed everything here except I have some Dataverse Choice columns to patch. I have used the following to patch all the changes at once. Doesn’t Patch the three choice columns I have. Does work for the text input column. Can’t quite get the syntax here. Thanks, Eric

If(
  CountRows(colUpdates)>0,
  Patch(
    TaxDatas,
    colUpdates
    );

Parwej Pathan
Parwej Pathan
1 year ago

Thanks Mathew! I read the statement “Each control in a gallery only counts as 1 control no matter how many times it is shown.” But I couldn’t find that in MS documentation. Recently , I was debating over Gallery control with 50 columns and 200 records with a colleague. Would really like to hear your opinion.

I really feel MS shall give # of control recommendation for Phone, tablet, desktop app separately.

beyondthebox
beyondthebox
10 months ago
Reply to  Parwej Pathan

I would disagree with this conclusion. Each control uses memory and each control in a gallery consumes memory based on the number of rows in the gallery. Also, the control limits in Power Apps are just a warning. There is not hard set rule. I have a business app with two screens. Once has 467 controls, the other 375 controls. Not all the controls are visible at any give time. The trick is to write the code efficiently and isolate the screens fully. My app runs very fast.

Alex
Alex
1 year ago

Hi Matthew, I am reading this article and found a typo in the “Cache Data in Collections and Variables” section: A cloud datasrouce must receive a connector call, perform a query and send a response back to the device before data can be displayed on-screen.

Thank you for your articles, it’s GOLD!

Mark Oram
Mark Oram
11 months ago

Hi, great article but I have a problem with an app that is connected to 110 sharepoint lists, the one to interact with is selected via a drop down on the first screen. It works fine for the general user however when trying to edit it takes over an hour tom open and even then I can’t change anything, saying an ongoing refresh operation is preventing it. I think the answer is to use an automated flow to create a collection using get items but I don’t want to slow the app for the general user, especially when it comes to writing changes back to the list. Is this viable?

Andrew
Andrew
5 months ago

“Each control in a gallery only counts as 1 control no matter how many times it is shown.”

I’m guessing you mean under the hood? When I use a gallery to replace repeated controls, I actually get a higher estimated complexity number (at least +1 for the gallery, sometimes more) even though it may also be performing better.

Tim
Tim
5 months ago

Hello Matthew,
Sweet as always, i was wondering how i can :
your way:

ClearCollect( colUpdateEmployees, Table( {ID: 2, FullName: “Alice Henderson”, Active: true}, {ID: 4, FullName: “David Wright”, Active: false}, {ID: 5, FullName: “Mary Allen”, Active: false} ) )

my way:
If(!IsBlank(LookUp(colPickUp1;ID = ThisItem.ID));Remove(colPickUp1; LookUp(colPickUp1; ID=ThisItem.ID));
  Collect(colPickUp1;ThisItem)
)

how can i use Collect(colPickUp1;ThisItem) to add hassent = 1 into the colpickup1
Like in your top code?

because i normally use patch in a slow way it seems
ForAll(
     colPickUp1;
     Patch(
           OnlineData;ThisRecord;
      {
        HasSent: 1
      }
     ))

I like to use the faster patch 😉
But no idea how i can add hassent in my collect(colpickup1, thisitem)

Thank you.

Peter
Peter
1 month ago

Hi Matthew, great article and really appreciate all the information you have provided.
I’m running into an issue that’s driving me crazy. I need to do a lookup inside a clearcollect call, and cannot get it to work. I copied your example from the N+1 problem, and am getting an error on the “thisitem” portion that says “name isn’t valid.ThisItem is not recognized”.
Any help would be tremendously appreciated

Powerapps.png