@datalearner The data row limit in a Power Apps Canvas App should apply to all data sources as far as I know. The default is 500, and you can raise it to 2,000. Suppose it's 2,000. Even when you use a fully delegable function call, such as a fully delegable Filter, I believe the data row limit might still apply, per individual function call. So even if there are 100,000 rows, and suppose 5,000 rows match your filter criteria, it's possible that due to the data row limit, only 2,000 records are returned in some cases (your mileage may vary).
If it were non-delegable, only the first 2,000 records out of the whole 100,000 records would be scanned, and only matches out of the first 2,000 would be picked, all the rest discarded, and that would be the highest level of inaccuracy.
However, delegation does not necessarily mean unlimited power to escape the data row limit in an absolute, indiscriminate manner. Even for SQL Server Connector. Even if all 100,000 records may be processed on the data source side, it is entirely possible that the data row limit applies to the filtered records, and there's no delegation warning because it is actually not a "Delegation" issue at that point.
Even if more than 2,000 records were allowed, this may start to slow down the app and it's not recommended. Many cases using Power Apps don't benefit from such a massive number of records being shown at once. Especially since we're dealing with support for multiple form factors, especially mobile devices, the sheer huge number of records becomes almost impossible to humanly process. It's best instead to design the app with lots of intelligent Filters and bring the number of filtered records well below 2,000, as low as possible and as seamless of a user experience as possible.
To clarify your point, the data row limit should exist even when using Premium connectors, the data row limit is sometimes called the "Delegation limit" but the concepts are distinct. There's actually just one concept, the "data row limit" but let's think of it as two concepts for simplicity. "delegation" and "data row limit".
Assume 2,000 is the data row limit again.
The "delegation warning" thing means it takes the data row limit and applies it to the first X records to manually process on Canvas App side, as there is no possibility for the integration with the data source itself to do this currently, so it is the most likely to be unusable for production when there's more than 2,000 total records in the data source, as only the first 2,000 records in order of the primary key are scanned and everything after is discarded. After that, any Filter or anything else is applied only on that set of the first 2,000 records.
However, even with no delegation warning, that's not an absolute escape or guarantee of data completeness. It's entirely possible you have more than 2000 records satisfying your filter criteria. It is also possible you get back only 2000 records in that case as that is the data row limit. There's no warning here, because there's no "Delegation" issue - the formula was fully delegated. However, no function is immune to the "data row limit". An individual function call returns up to a max of the data row limit. It's up to you to intelligently design the Filter so it returns less records. The data row limit is applicable to all data sources as there aren't unlimited resources on the client, especially since we're dealing with diverse clients like mobile devices, tablets, etc.
2,000 is the highest you can raise it to (note it's 500 by default actually).
There are tricks to try and force it to go higher, most of these rely on the underlying idea that it's a max of the data row limit per function call. So some tricks try and force the collection to be larger such as by using Collect calls inside ForAll in batches, and leaving the outer table returned by ForAll completely unused, but I almost never recommend things like that except in very rare cases as these are generally bad practice, bad habits and may lead to poor performance of the app.
Whenever we see a ForAll that is outside a Patch, and the outer ForAll is unused, this is a common example of this being done frequently, sometimes without even realizing it. However it's almost always better to put the Patch outside the ForAll instead, as we minimize the number of calls to the data source (Patch) from the number of iterations in ForAll, down to just 1 call, which is a big difference, much better performance, and just makes much more sense.
Note that when you want to do partial searches on text fields, SharePoint List data source doesn't have an easy way to do this directly from the Canvas App (we need workarounds like using a Cloud Flow) whereas the "in" operator on Text from Canvas App Power Fx formula is directly and fully delegable on SQL Server connector.
SQL Server has a data row limit just like SharePoint because Canvas apps have the data row limit for any data source.
So in your case if you like SharePoint, you can go ahead and use SharePoint if you can figure out how to make it work for you. SharePoint is very powerful, and I recommend it even for very large cases, if you can manage it 🙂
Hope it helps @datalearner
