How does the 500-2000 row limit apply to a gallery table getting data from a custom connector?

[aec2018]

I'm creating a canvas PowerApp that uses a custom connector to call our GraphQL endpoint to retrieve data that populates a gallery table. I would be placing the returned data in a collection (using the ClearCollect() function). Some data has the potential to have 10k rows, possibly more. Does the 500-2000 row limit (depending on the app settings) apply to the gallery table entirely? Or is this limit only for non-delegable functions? Meaning my gallery table can have 10k rows loaded but any filter functions will only work on the first 500-2000 rows in the Collection? 

[RandyHayes · Accepted solution]

@aec2018 

As its name implies - Delegation is about passing a narrowing query to a datasource in order to reduce the results.  If you pass a delegable operation to a datasource, then it will return results based on that.  If you attempt to use a non-delegable query against it, then the datasource will not be able to do the query and will only be able to return all the rows of the datasource to the app in order for the app to then attempt to perform the query.

Delegation only refers to actions against a datasource. 

A table in your app (either in a variable, or collection) is in-memory and there is no delegation concern. 

Galleries are quite different.  If they are based on a datasource, then they have the ability to "chunk" data returned. So, technically they are not limited and they do not participate in the Record Limit setting in the app.

I hope this is helpful for you.

[aec2018]

Thanks for the response, @RandyHayes . That's very informative. I want to get a better understanding of what you're saying. My gallery table's data source will be the collection I create when the response from the custom connector returns. If this response has 10k or more rows, the collection will have no problem holding that amount of rows? (I'm guessing that'll have an effect on performance, at least.) The gallery will be able to be filtered per column(s) so the Items property will be Filter(myCollection, [...userTextFilters]). Since the data source is the collection that's in-memory, the Filter function will work on all 10k rows and not the 500-2000 limit, correct? So the row limits don't apply in our scenario.

[RandyHayes]

@aec2018 

Yes, in general - it is not a very PowerApps way to go to display huge amounts of data. 

To understand that process with a Gallery - Think about a search engine (Bing or Google for example).  When you search one, what would it be like if your search returned 10k results and then the search engine needed to build a massive html page to return to you that had every result in it.  You would sit there and wait for quite some time to get your results and then, if the first one is what you wanted, then it just wasted a lot of time and resources to get it.  So instead, the top X number of results is returned quickly, then as you want to go further, then more is returned and so on.

Same thing with a Gallery based on a datasource, it will return the first 100 results from it, then as you scroll down, it will load more and more.  This way a user is not bombarded with so much information they don't want and have to sit and wait a long time to get it.

If you use a collection you will severely impact performance in the app as, not only will you have the initial loading of that collection to contend with, but then the Gallery will display all of it as it will not page it like a datasource.

As for the filtering of the collection - yes, you have NO limits on the collection (except available memory and resources on the device).  Row limits ONLY apply to data interactions external to the app - like a datasource.  And as a side-note, that is delegable or not.  Meaning, if you have a delegable filter statement for a datasource, but it will return 5000 records, then you will not have a delegation warning, but your filter will only return X number of records - where X is the record limit defined in the app settings.

[aec2018]

@RandyHayes 

If you use a collection you will severely impact performance in the app as, not only will you have the initial loading of that collection to contend with, but then the Gallery will display all of it as it will not page it like a datasource.

---

So in this regard, what would be the best way to handle our data coming from an API response through our custom connector? We're currently in talks about our API returning paginated results so that not all 10k+ records get returned at once. If we can return a more filtered response, what's an ideal limit to hold in a collection that won't adversely affect performance? (I apologize if this is straying off the topic.)

[RandyHayes]

@aec2018 

Yes, getting the API to not return so much data and have a way to page it is great!

The amount that you would want would be dependent on the data being returned.  If it is heavy (lots of columns with lots of data) then I would keep the limit somewhat small and you'd most likely want to do some performance tests first on how long it takes for X number of records to transmit.  If the data is light, then you can probably pull more without a great performance impact.  Just depends on the data.

[aec2018]

@RandyHayes 

So this kind of ties back to my original post. Because our data will be too big to be held in an in-memory collection since the row limits don't apply to us but users will want to filter on the entire data (filtered in-memory + unfiltered in our db), our by-column(s) filtering will most likely have to be done by API calls rather than using the Filter() function on the collection since our collection won't have all of the data?

By multiple column filtering, I mean this scenario in our PowerApp:

[RandyHayes]

@aec2018 

Yes, ideally you would want to have some ability to delegate to your API.  So, being able to provide some filtering ability that will happen server/connector side rather than having to bring it all to your app.

This all plays in to the reasons for record limits in PowerApps to start with.  If you had a 10k row database with heavy data, and you could not delegate a query to it, then technically, it would need to return all 10k rows to your app so that the app could apply the criteria.  This would be highly detrimental to the app and also to the entire platform as the cloud services would have a tremendous overhead to them.  A decision was made somewhere that 2000 was a good limit to cut off on.  Of course that then goes into the whole "partial" data and so forth.  That is where delegation becomes key.  If I can send the criteria to the data source and it can perform the filter there, then it will only need to return the results of that.

So, taking that to the custom connector to an API, having the ability to do the same would be paramount.  This way the connector will only return a preset amount of information and thus not overload the app nor put performance into a tail spin.