Limit on number of rows returned from a SQL view?

[BrownMango69]

I have read the the there is an limit on SP rows returned to a collection (2000 lines) and that there were no limits on the # of rows returned from a SQL table to a collection. But it appears that my SQL view (which has over 3000 rows) will only return 2000 rows to my collection. Never read anywhere about a limit on the number of rows from a view...

Can anyone else confirm this?

[Pstork1 · Accepted solution]

There is no limit in SQL to the number of rows returned.  There is a limit in PowerApps on the number of rows returned no matter what the data source. A collection can have more than 2,000 rows, but you need to find a way to load the collection in batches of less than 2,000 rows.  And of course all that is assuming you've upped the default limit in PowerApps, which is normally set to 500.

[Pstork1]

Check this article and you will see that they don't specify different row limits for different data sources. They do say that you can avoid the results of a row limit by using a delegable function on a data source with higher numbers of rows to get the number of rows returned to lower than the limit.

[Pstork1]

Forgot the article link

https://powerapps.microsoft.com/en-us/blog/powerapps-data-row-limit-for-non-delegable-queries/

[v-xida-msft]

Hi @BrownMango69 ,

Currently, within PowerApps, the Collection is not a delegable data source. There is a limit with the connection when populate data from other data sources -- you could only populate at most 2000 records from other data source into a Collection.

I agree with @Pstork1 's though almost. You could consider bulk-load data from your SQL View into multiple individual collections in your app, then merge the multiple individual collections into a Single one collection.

Please check and see if the alternative solution within the following thread would help in your scenario:

https://powerusers.microsoft.com/t5/General-Discussion/Pulling-in-large-ish-SQL-tables/m-p/243777#M7...

Set the OnStart proeprty of the App control to following (I assume that there are 10000 records in your SQL View):

Concurrent(
    ClearCollect(col1, Filter('[dbo].[YourSQLView]', recordID >= 1 && recordID <= 2000)),
    ClearCollect(col2, Filter('[dbo].[YourSQLView]', recordID >= 2001 && recordID <= 4000)),
    ClearCollect(col3, Filter('[dbo].[YourSQLView]', recordID >= 4001 && recordID <= 6000)),
    ClearCollect(col4, Filter('[dbo].[YourSQLView]', recordID >= 6001 && recordID <= 8000)),
    ClearCollect(col5, Filter('[dbo].[YourSQLView]', recordID >= 8001 && recordID <= 10000))
);
ClearCollect(MergedCollection, col1, col2, col3, col4, col5)

Note: The recordID represents the Primary Identity(1,1) type column in your SQL View.

After that, save and re-load your app (fire the OnStart proeprty of the App control), then you could use the MergedCollection as data source within your app instead of your original SQL View data source.

Please consider take a try with above solution, then check if the issue is solved.

Best regards,

[Anonymous]

Hello @v-xida-msft 🙂
Your code is really helpful, BUT, I have two remarks :

1) For your solution, you have to have a unique column number. My IDs are GUIDs with alpha numeric characters.

2) Your solution is absolutely not Evolutive ! You assumes that the records are only 10 000. This could be the historic. BUT what if I create one more item ? How to predict and create a new collection if the number of items are superior ?

I thought about doing a Count of my whole rows, but even there it is absolutely impossible, because all the Count methods (CountRow, Count, CountA, CountIf) are not delegable ! So they won't work with tables of more than 2000 records !

Here is my question : how to segment my datas in collection, but doing it automatically, evolutively ?

Thanks a  lot !
Have a nice day !

[v-xida-msft]

Hi @Anonymous ,

Yeah, you are right.

For your first question, if your SQL View does not contains a Identify(1,1) type column, the alternative solution I provided above could not achieve your needs.

Currently, if you do not include a unique Identify(1,1) type column in your SQL View, I afraid that there is no way to achieve your needs in PowerApps currently.

For your second question, if you want to segment your data in collection automatically, evolutively, please check and see if the following blog would help in your scenario:

https://blog.coeo.com/davidmorrison/powerapps-500-record-limit-delegation-and-how-to-work-around-it

Best regards,

[BrownMango69]

Away on vacation - sorry for the late response. Thank you everyone for answering.

I didn't realize that collection were non-delegable sources. My app was working great till I hit 2000 records. I'll implement a solution similar to the coeo one...

Thanks!

[G0Springs]

How would you up the default limit in PowerApps, from 500 to something higher like 1000?

[Pstork1]

You can increase the data row limit up to 2000 in the General tab in settings for the App.