cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BrownMango69
Level: Powered On

Limit on number of rows returned from a SQL view?

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Dual Super User
Dual Super User

Re: Limit on number of rows returned from a SQL view?

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.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

View solution in original post

7 REPLIES 7
Dual Super User
Dual Super User

Re: Limit on number of rows returned from a SQL view?

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.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

View solution in original post

Dual Super User
Dual Super User

Re: Limit on number of rows returned from a SQL view?

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.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
Dual Super User
Dual Super User

Re: Limit on number of rows returned from a SQL view?

Forgot the article link

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



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
Community Support Team
Community Support Team

Re: Limit on number of rows returned from a SQL view?

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,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Gaëlle
Level 8

Re: Limit on number of rows returned from a SQL view?

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 !

Community Support Team
Community Support Team

Re: Limit on number of rows returned from a SQL view?

Hi @Gaëlle ,

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,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
BrownMango69
Level: Powered On

Re: Limit on number of rows returned from a SQL view?

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!

Helpful resources

Announcements
Better Together’ Contest Finalists Announced!

'Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Join THE global Microsoft Power Platform event series

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

thirdimage

Microsoft Business Applications Virtual Launch

Join us for the Microsoft Business Applications Virtual Launch Event on Thursday, April 2, 2020, at 8:00 AM PST.

thirdimage

Community Summit North America

Innovate, Collaborate, Grow - The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors
Users online (8,532)