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

[WebPortal]

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!

[Pstork1]

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.

[WebPortal]

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

[Pstork1 · Accepted solution]

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.

[MasterOffice365]

I recently uploaded a demo video to YouTube wherein I showcase the app can actually even return as many as 5000 rows using the SharePoint v2 Graph APIs (10k+ files in Document Library. I equally set a Gallery control in the app that then literally displays the 5000 items returned by that flow in that gallery including thumbnail images for each single item in the gallery. There is fully automated paging enabled by the SharePoint Graph v2  API.

The intent for demonstrating that capability in the video was primarily to show any interested App Makers what happens to the responsive of the app when you load as many 5000 items in a gallery, In the demo, there a noticeable degree of degradation in terms of the responsiveness the overall app when that gallery controls contains 5000 items. Having said that though, if you wait long enough the app eventually becomes at least reasonably responsive. In the demo, I equally demonstrate the automating in the app, and is theoretical degree of degradation in the overall performance / responsive of the app.

With that said though, because the app is exceeding responsive when the page size threshold is configure to return anywhere between 500-1000 rows, and with automating paging you be to transverse through all 10k+ files in that Document Library well before you could possibly do should the page size threshold be set to 5000. Whether there are 10k files or 200k files in the Document Library would make absolutely no difference as long as you ensure you configure the indexing on that list in order to make whatever columns are indexed delegable.

If you worked much with PowerApps, make sure you check out the Optimize canvas-app performance in PowerApps guidance and the demo video of the app I created, as well as the corresponding blog I posted associated with that demo app, if you have an entire day to get through it  😂

http://masteroffice365.com/powerapps