Canvas App: Architecture in large organisation using SP question

[SebS]

Hello,

What will be the best approach for performance in large organisation with more than 7 sites:

I'm aware about benefits of Dataverse and SQL but this question is purely for SharePoint as Data Source approach.

App: Canvas

Data Source: SharePoint

Multiple Lists linked to single app 4 to 15 to each app but not more than that

Some Main List will contain between 1000 and 10000 records there is small number of lists what will grow above 40k+

Apps will be created with Delegation challenges in mind.

Complex type Columns will be avoided except Person or Group Column

Should I have Central Lists and collect Data from all sites with column indicating site tag or id, or it will be better to create instance of the App and List for each site? (I understand challenge and time needed to manage instance of 7 or more solutions when update will need to be pushed) 

In a model where for example:

All apps will share one list for employees (4000+) to populate controls required inputs from it, and rest lists what will generate data will be split by each site and then join by flow or Data Set will be created do Drive any Dashboards or Reports if needed.

Does this approach provide better performance? I need to consider number of calls to SharePoint list I get. Lots of tasks will be delegated to SharePoint like pre filtering but when I think in same time there will be 100 users doing a call on start of the app or loading filtered data to collection that will overload SharePoint list, so splitting them have more sense, if they will grow to 10k or more records in a year before archive or I'm wrong?

Any Feedback or Ideas welcome.

[Scott_Parker]

I can't comment on the performance of 100+ people using a SharePoint list all at once, but I doubt it will be an issue. I think splitting the list before seeing an issue would be premature optimization. SharePoint should be able to handle your quantity of records fine. 10k records is pretty small in the grand scheme of things.

I think the bigger question here that you didn't mention is how are you managing permissions? 4000 is a lot of people. Your decisions on whether to have individual lists or not may be driven more by what O365 groups or SharePoint groups are being maintained by each SPO site. It also depends on if you're creating an app with a narrow audience or a broad one. Are you going to be relying primarily on the directory to determine roles, managers, etc? Are you going to be giving unique permissions to every item created (based on who created the item, etc). The permissions question does tie into the size of the lists because there is a limit of 50,000 unique permissions for items in a list or a library.

[SebS]

@Scott_Parker 

You are right, permissions and access are essential, but of 4000 employees, only around 10% have an O365 E3 licence, and from that, around 60% will run the apps from this project, so AD Security groups will be efficient in managing sharing Apps access and access to lists.

The main reason I'm asking about performance is that I have had a bad experience with a complex app (not my design, and probably I should deep dive into finding out if it's not related to bad practice) that is connected to a list that stores 7000 records a  week and also updates 2000 records 9 times a day now if I will multiply that database times 7 I want to make sure a filter call to extract 2000 records from  40000 will not create 60 s waiting time for the user. I understand 100 users at the same time is not much, and SharePoint easily can handle this volume of calls, but it's just my curiosity what other experiences so far.

I'm considering creating 50k to 100K records and running a test with 10 to 40 users and seeing with my own eyes how this impacts performance, but I was thinking it's worth asking here about other developer experience, Ideas or approaches when developing big-scale Apps using canvas and SharePoint.

[Scott_Parker]

Gotcha, yeah if you have good AD groups then that is definitely the best solution.

I personally haven't pushed Power Apps that far, yet. It sounds like the complex App you're referring to might be making a lot of individual calls. Potentially many separate LookUp() calls or using Power Automate to take many SharePoint actions? Potentially exceeding the 600 calls per minute throttling limit per SharePoint connection. In the case that the App is calling a flow, I would check if the flow is using the run-only user's connection or a connection reference made by another user. Using the connection reference could result in throttling that connection.

I agree that if there aren't any egregious errors in the app you mentioned, a test would be warranted.