Table Of Contents:
• Load Multiple Datasets Concurrently
• Write Formulas That Use Delegation
• Cache Data In Collections And Variables
• Limit The Size Of Collections
• “Batch Patch” Multiple Changes To A Datasource Table At Once
• Reduce Code In The App's OnStart Property
• Minimize Number Of Controls On A Single Screen
• Enable DelayOutput For Text Input Controls
• Do Not Reference Controls On Other Screens
• Eliminate 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
Did You Enjoy This Article? 😺
Subscribe to get new Power Apps articles sent to your inbox each week for FREE
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.
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 !!
Piotr,
Fixed. Thanks for reporting the error
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.
Gil,
Fixed. I meant to use colAccounts rather than accounts.
I just want to send a huge THANK YOU for all of these helpful guides you create and share with the community!
Haylee,
You’re welcome. I’m going to put all 11 sections into a PDF for offline use and send it to my subscribers next week. Enjoy!
Присоединяюсь!
Voltron,
What did you join?
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
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,
The Collect function will just get the 1st 500 records unless you increase the delegation limit in advanced settings. Maximum is 2,000 records.
Yes, you can move the code from OnStart to the OnVisible of your 1st screen. I suggest you make this a loading screen with a spinner or progress bar if it takes more than a few seconds (link: https://www.matthewdevaney.com/power-apps-loading-spinners-saving-spinners-and-progress-bars/)
Instead of using the Navigate function, you should put the screen name in the StartScreen property of the App object.
Hi Matt,
I have deep linking function and I just included it on my OnVisible property.
Jay,
Good stuff.
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,
The first question I would ask is, why bring 100k records into a collection? For a table of this size I would be reading/writing data directly from/to the datasource. At the most, you’ll want to Collect the subset of records the user is currently viewing. I personally would not try to improve the loading time. I’d attack the root issue of what’s the need for this anyways?
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.
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.
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
);
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.
Parwej,
Microsoft can’t give a precise number of controls because each control uses a different amount of memory. Example: a nested gallery uses way more resources than a single text input. That’s why Power Apps relies on a screen complexity score. Unfortunately, you won’t see a warning raised until you surpass this score and they don’t really tell the calculation details.
Using 50 columns in a single gallery seems like too much. Use the ShowColumns function to reduce the amount of data you are loading into the app.
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.
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!
Alex,
Typo is now fixed. Thank you for reporting 🙂
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?
Mark,
110 SharePoint lists in an app is too many. Power Apps official documentation suggests no more than 30.
https://learn.microsoft.com/en-us/power-apps/maker/canvas-apps/performance-tips#limit-data-connections
“Don’t add more than 30 connections in one app. Apps prompt new users to sign in to each connector, so every extra connector increases the amount of time that the app needs to start.”
Yes, offloading your code to a Power Automate flow would be better. Also, try to remove as much code as possible from the app’s OnStart property and use Named Formulas for constants instead of variables.
“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.
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.
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
Peter,
ThisItem is only for galleries. You likely want to substitute it with ThisRecord instead.
https://powerapps.microsoft.com/en-us/blog/formulas-thisrecord-as-and-sequence/
hi Matthew, I enjoyed your article but I don’t understand the very last snippet. For me ‘ThisItem’ it not needed. Besides it creates an error. It should be:
ClearCollect(
colGalleryData,
AddColumns(
colContacts,
“AccountName”,
LookUp(colAccounts, ID=AccountID, ‘Account Name’)
)
);
In my PowerApps application, I have implemented transition screens using a single background image. To create the transition effect, I am using multiple transition images on the Screen. However, I have noticed that it takes a considerable amount of time to load these images when the button is selected, resulting in a delay.
I would appreciate your guidance on how to address this issue and improve the loading time of the transition images. If there are any alternative solutions in PowerApps that can help optimize this process, please let me know.
Thank you for your assistance.
Best regards,
PowerApps user
Hi Matthew, any advice or best practices with regards to multiple screens and datasources? Ive got 10 datasources with the same table. Is it better to use
1 screen per datasource OR 1 screen for many datasource since the screen function is the same.
Also
Is it better to use datasource directly or use a collection for better performance?
Claire – this is my question, too…Matthew: is it better to use the data source directly or use a collection for better performance? And aside from performance, I am worried about reaching request limits by directly referencing the SharePoint each time someone views a screen. Maybe that is unreasonable because 40,000 calls in 24 hours SOUNDS like a lot but I’m worried it might add up faster than expected if the user is interacting with lots of different apps in a given day?? (https://learn.microsoft.com/en-us/power-platform/admin/api-request-limits-allocations) That’s why I am not using Formulas in my apps yet – it might be faster but it causes more requests to SharePoint!
Question: Significant Load Time Difference Between Android and iOS Devices in PowerApps
Hi everyone,
I’m experiencing a significant difference in load times between Android and iOS devices when using a PowerApps app. On iOS, the app loads within 8 to 15 seconds, whereas on Android, it takes around 1 minute and 30 seconds to load.
Here are some details:
Has anyone else encountered a similar problem, or does anyone have suggestions on what might be causing this discrepancy? Any help or guidance would be greatly appreciated!
Thanks in advance!