cancel
Showing results for 
Search instead for 
Did you mean: 

Delegation and data row limits: Please let us turn off or drastically turn up the limit

I would like the option to turn off the data row limit for non-delegable queries. I would like the option to accept that my app will take longer to load in the cases where I have very many records, and I would like the full results of multifunction formulas regardless of the time it takes to get them.

I have spent an incredible number of hours working around delegation limits in complex formulas, and the result is that my app takes much longer to load anyway. I have a suspicion that these workarounds are not making my app any faster than if there were no delegation limits at all. Please give me the option to accept the loading time without extra hours coding workarounds that are not any faster anyway.

If the limit cannot be eliminated or drastically raised from 2000, another very good option is to allow CDS Views to accept parameters from inside the applications. There is already an idea for this here:
https://powerusers.microsoft.com/t5/Power-Apps-Ideas/Make-CDS-Views-accept-parameters/idi-p/525667

Status: New
Comments
JLincoln
Advocate I

Could you post an example of the kind of query which is difficult to delegate? What's your use case? We may be able to restructure your query to delegate more efficiently and make setting up the query faster to boot.

Boneckrh19
Kudo Kingpin

There are dozens of nondelegable queries across multiple applications. It is turning into a fulltime job solely to go through each issue and see if it can be adjusted to work around the limits, and that is made all the more difficult because most of those issues are not being flagged as problems in the app at all. They can only be found by setting the data limit to 1 and going through every single lookup/filter/collect/other function to see if we are getting the correct number of records.
(Idea for a better warning system here: https://powerusers.microsoft.com/t5/Power-Apps-Ideas/A-more-thorough-warning-system-for-delegation-a...)

In the places where there are no other options, multiple forall collect statements must be used to create large collections for use in the app, which drastically slows everything down.

Going through every single function in every application is not a solution to this problem.

JLincoln
Advocate I

How is your user experience on the apps that have long delegation time? It's probably wasting more company time than just fixing the root cause (query bloat) to have your users waiting through long load times.


I had an app that was opened 18x a day and I was doing a calculation for each basket to find every object that went in there so I could filter dropdowns. I started pre-calculating this with Flow as an additional field on my datasource and my app load time went from 15 seconds to 2 seconds. 240 workdays/yr * (13/3600)hrs * 18x a day *$40/hr = $625/yr just in load times. Imagine if I was loading this every time the user navigated or landed on a specific page; that's a LOT of time spent.

Microsoft has limits so that the user experience doesn't get bogged down so much that a company won't want to use PowerApps anymore. Going through every single function is indeed the solution to this problem and finding best practices to make sure queries delegate is important. I have some queries with 15 filter statements on them based on logic going on in the app and they don't take more than 4 or 5 seconds to pull 2000 records. If you're suggesting that you need more than 2000 records at a single time then you need to set up another table that uses Azure Functions or Flow or some other calculating mechanism to aggregate data so that you don't need to calculate it every single time that a user opens your application.

Boneckrh19
Kudo Kingpin

Flow is something my company would like to avoid at all costs. Flow does not play well with app permissions and Solutions. We are trying to keep all things contained in the CDS and PowerApps.

Those Microsoft limits are what is slowing us down at this time. That is the "root cause", so to speak. I personally don't enjoy using PowerApps because of these limits and the extensive workarounds we have to do to avoid them. Going through hundreds of complex formulas has made me decide that PowerApps is not ready to be used on a large scale at all. If that issue could be addressed, PowerApps is exactly what my company wants out of an app-building platform.

JLincoln
Advocate I

Correct, Flow doesn't play well with app permissions but you can do calculations with it outside of the app. I have a few that run on recurrence and do the calculations outside of the app on the CDS entities so that I don't have to do calculations within PowerApps. One can also set up flows as functions which can do calculations external to the app; I have one set up that acts as a general function that multiple apps can use to save pictures as attachments, for example. I understand that the permissions are kind of hard to work with at first, but 'avoiding them at all costs' is literally costing your company money in load time.

I have set up maintenance apps (Canvas apps) that completely replace the functionality of our old preventative maintenance software and all of those formulae delegate. We have around three thousand PMs to track and it's workable. If I didn't set up delegation properly then the load time would be oppressive and it wouldn't be a good replacement for our old software.

MS isn't slowing you down. There are some things that need to be worked around (picture saving, printing, other features that haven't been created), but the root cause is failing to normalize tables and not structuring queries in a way that can offload the query load to the database itself.

I again offer to help look at your queries and see where the issue is. Sustainable practices in programming are important (and make no mistake, this is definitely programming), and there are always going to have to be ways you work with the limitations of the code to get your desired end result.

Boneckrh19
Kudo Kingpin

We have no issues with calculations. The problem is that we need to pull more than 2000 records so that we can filter those records inside the app based on what the user indicates they want to see. Of course one person does not need to physically look at more than 2000 records at once. The problem is we cannot give them the records they do want to see because filtering the table does not give complete results.

Ideally CDS Views would just be able to accept parameters from the apps, but that is currently not an option.

JLincoln
Advocate I

And something like

Filter(

Datasource,

OR( IsBlank( FilterDropdown), FilterField = FilterDropdown.Selected.Value ),
OR( IsBlank( FilterDropdown2), Filter2Field = FilterDropdown2.Selected.Value ),

)

won't work? That's delegable and allows you to filter the records on the data pull.

Please post some of your code. Delegation takes a minute to get but it's both powerful and nonintrusive when you get the hang of it.

SkiDK
Solution Sage

@JLincoln the problem is whenever you need to pull more than 2000 records, your suggested code won't do because it will never pull more than 2000 items, even if it's delegable.

 

I do think it is better to separate the data you load in your app. Having 2000 records in a table really is overkill. That applies to any app for that matter. A way to overcome this is to initially load as few records that are really needed into a collection (keep it under 2000 based on conditions), and once your need records that aren't loaded, collect them by means of a filter or a search (which are of course delegable) into the same collection (and check that you don't collect the records that already exist in the collection). This way you can have 2000+ records in your app. Know that collections are fast, but loading data is not. Loading 2000 or more records initially really is a waste of time. Split it up so that the user only needs to wait for the data that is needed. It's timesaving for the user, increases performance and will increase user experience too. But okay, It's a bit more work for the developer, nonetheless doable. 

 

You can see the delegation as a best practice warning. 

Boneckrh19
Kudo Kingpin

Prior to the user choosing what to filter on, we don't know what "conditions" should be used to get that initial under-2000-list. Also, only loading records as they are asked for means stopping the user to make them wait for loading repeatedly during use as opposed to once in the beginning. I personally would rather wait longer for an app to load initially than be stopped every few actions while using an app.

JLincoln
Advocate I

You can delegate filters. Refer to my filter statement above.

You could also reference this iterative data pull by Mr. Dang which uses multiple delegated pulls to get a large number of records (more than 2000) into a single collection, and you could filter that collection.

http://powerappsguide.com/blog/post/delegation---how-to-overcome-the-5002000-row-limit-with-collecti...