PowerApps data row limit for non-delegable queries
We are happy to announce the ability to control the size of the local record limit store up to a maximum of 2000 records. This is an experimental feature available in release 2.0.750. Today in PowerApps if we cannot delegate a query to a back-end data source (filter, sort, etc.) we only retrieve the first 500 records. We store those records locally, and then perform the data operations on just those 500 records. If your query is limited in this way, a blue dot will appear on the control you are using with a notification that your results may be partial.
Certain types of connectors to data sources do support delegation and others do not (e.g., the OneDrive Excel connector.) This new data row limit is an application global setting that will affect all non-delegable queries in your application. This limit has no impact on non-server based data sources (Collections, imported Excel spreadsheets, etc.) as delegation does not apply. You will find this new feature in the PowerApps Experimental features section (File > App Settings > Experimental features.)
You may set the values for the data row limit from 1 to 2000. We do not allow values above 2000 may because a higher value is very likely to adversely impact system performance. Values close to the 2000 limit may also adversely affect performance. Test your system to find the optimal value for your application. If your connector has this non-delegation query limitation, and you know that the number of records you need from your data source is 2000 or less, you can set this value to 2000 and see all your records. You will still see the blue dot on your controls though because the connector has an overall limitation. If you need to use more than 2000 records of data from a data source that does not support delegation, you will need to move your data to a data source type that does support delegation.
Here are two examples of how you might use this feature:
- If your data source has less than 2,000 records and you know it will not grow beyond this number, turn up the setting. If you have 1,000 records in a reference table, this might be perfect for you. But be careful: as the number of records goes up, your app may slow down waiting for all that data to come in, especially on a mobile device. Consider too the “width” of the records (number of columns) – the wider the record the less records you will want to retrieve.
- If you want to ensure that your app will always scale well, turn down the setting to 1. This will ensure that your app only delegates queries, or you will only see one record at a time which should obviously be wrong. This helps avoid building a proof of concept app with a small data set only to have the app run into trouble when deployed with a larger data set.
This a first step in helping better manage the record store and giving you better control over your application’s performance behaviors. We look forward to any feedback you may have on this feature.