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
Boneckrh19
Kudo Kingpin

We have already tried that exact code referenced in the blog post. Not only does this require an auto-incrementing number field, (which is not possible without manual implementation, as the auto-number option in the CDS is considered a string field,) but that code is not quick. Although we would rather have all the loading time be in the initial OnStart than during use, ultimately I would rather not need to code workarounds at all, which is why I made this idea in the first place.

JLincoln
Advocate II

You're going to have to workaround *something* in every language or platform that you work in; a platform developer can't optimize for every scenario. It's like indexing lists; if you index it for use case A then use case B won't be optimized and vice versa.

PowerApps is optimized for quick data pulls because users aren't willing to sit there while 20,000 rows load and it isn't optimal to pull that many from a SQL table developer standpoint. It's a middle ground that protects users who have no idea how to code a pull correctly (protecting the database) while still offering workarounds that allow more experienced / savvy coders to get the results they need.

Try something like this
Set(varIterationCount, RoundUp(last(datasource).ID, 0)/2000);
ClearCollect( colSequence, Sequence(varIterationCount, 0);
ForAll( colSequence, Collect(colActualData, Filter(datasource, ID>=1+Value*2000, ID<2001+Value*2000)));

It's essentially the same solution as Mr. Dang's but it takes advantage of Sequence and it's maybe a little bit more streamlined. The fact that this takes a long time to run is because you're pulling so many rows.

Boneckrh19
Kudo Kingpin

If the only solutions offered are ones which require a numerical unique identifier, the CDS should offer a numerical unique identifier field. Autonumber is a string field type, and the Value function is nondelegable.

davidmckenzie
Advocate I

Does CDS allow calculated columns? If so, why not create a numeric column calculated as VALUE(Autonumber)?

 

Alternatively, change the filter in the loop to a 'startswith' and using text values "10","11","12" etc. to "99" - this does mean 89 server calls, but will allow 100880 rows maximum (assuming no gaps in numbering) as row 100881 is the 2001st number beginning "10". Not particularly efficient, but you could reduce this if you know your table only has, e.g., 30000 rows to get "10","11" etc. to "29" then "3","4","5" to "9" (since there are only 1112 numbers beginning with "3" up to 30000).

 

However agree that the 2000 limit should be increased or removed; if a developer wants to leave set as 500 or 1000 or 2000 they would still be able to do this to save bandwidth, server load, app load time etc.

JLincoln
Advocate II

@davidmckenzie @Boneckrh19 

I actually had a similar issue with SharePoint lists over 5000 not delegating the ID when doing more complicated lookups. Here's my solution which occurs on committing a row with the app, and it should work for you in CDS as well. (Calculated columns in CDS are not delegable just as SharePoint calculated columns are not delegable.)
//Save the new row to the database
UpdateContext{
  varLastSubmitted:
    Patch(
      Datasource,
      Defaults(Datasource),
      {Record Info}
    )
});

//Update the delegable mirrored ID field
Patch(
  Datasource,
  LookUp(Datasource, ID = varLastSubmitted.ID),
  { DelegableIDField: Value(ID) } //Note that this is a Number / Int type
)

This kind of workaround isn't super duper stable (what if someone goes and manually deletes the DelegableID contents !?), but it works well enough.