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

What datasource should I use to connect to a 50K row table?

In creating an app connected with a datasource with 50.000 rows of data, what kind of connector may I use:

 

  • Excel? It doens't read beyond 2.000 rows and it doens't support delegation.
  • Sharepoint List? What's the limit of rows? I believe it is 30.000.000 and it handles delegation, is this viable?
  • Common Data Service? Requires an additional license, beyond the seeded Office 365, but will it handle 50k rows?

Thanks for helping!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Dual Super User
Dual Super User

Re: What datasource should I use to connect to a 50K row table?

1) Yes SharePoint can handle 50,000 rows.  The default limit on retrieval is 500 rows, but this can be increased to 2,000.  Increasing to 2,000 may impact performance.  But that is true for any data source, not just SharePoint.

2) Yes, to use CDS you would need premium licenses for all of your users.  How much that would cost is dependent on your other licensing.  If you have Dynamics 365 licensing its already covered by that for most Dynamics products. If you are only building one app you can buy App licensing at $10/app/user/month. If you want to build more apps then you would need standalone licensing at $40/user/month.



-------------------------------------------------------------------------
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

3 REPLIES 3
Dual Super User
Dual Super User

Re: What datasource should I use to connect to a 50K row table?

First, none of the data sources for PowerApps will handle 50,000 records without delegation.  It simply isn't practical to deal with that many records on the local device at a time.

 

Second, the tradeoff between data sources like CDS or Azure SQL vs SharePoint isn't really a question of capacity.

  • CDS and SQL will require additional licensing, but provide better delegation and relationships between tables
  • SharePoint has sufficient capacity, reasonable delegation, and is included in the Office 365 licensing

So it really comes down to a question of licensing versus the complexity of your data.  If your data requires relationships between tables or entities use SQL or CDS. If your data could be leveraged in Dynamics use CDS. If your data is more flat and you want to avoid additional licensing costs use SharePoint.



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

Re: What datasource should I use to connect to a 50K row table?

@Pstork1 thanks a lot for your help!

 

So, you confirm that Sharepoint can handle 50K rows and I can fetch 2.000 at a time with delegation?

(I agree, without delegation, this would be a mess...)

 

I belive CDS would be a better solution, but since we have one developer (me) and 40 users, do we need 41 licenses or is one enough?

 

Thanks again for your support!

Highlighted
Dual Super User
Dual Super User

Re: What datasource should I use to connect to a 50K row table?

1) Yes SharePoint can handle 50,000 rows.  The default limit on retrieval is 500 rows, but this can be increased to 2,000.  Increasing to 2,000 may impact performance.  But that is true for any data source, not just SharePoint.

2) Yes, to use CDS you would need premium licenses for all of your users.  How much that would cost is dependent on your other licensing.  If you have Dynamics 365 licensing its already covered by that for most Dynamics products. If you are only building one app you can buy App licensing at $10/app/user/month. If you want to build more apps then you would need standalone licensing at $40/user/month.



-------------------------------------------------------------------------
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

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

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

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 342 members 5,556 guests
Please welcome our newest community members: