List view threshold of 5000 items must be one of the most long-standing issues in SharePoint Online (SPO). It was there 4-5 years ago when I started out with SPO and it's still there. Recently, I had to deal with it again and decided to put together this summary that may help.
Consider a list or library with over 100,000 items:
- This point is to clarify that list view threshold still applies even with modern experience UI in SPO. Modern UI only displays a small subset of data first and more items are displayed as user scrolls. Despite this, if there exist more than 5000 items in a view, the list view threshold error appears.
- 5000 item limit applies to containers and a folder is a container. Hence, a library can function normally if each folder within the library has less than 5000 items e.g. 100,000 items spread across 20 folders.
- Indexing columns improve performance and one way to get around 5000 limit is to index your columns and then create a view that filters on the index column to try to limit the number of items fetched in view below 5000.
- Display all items in view but only show indexed columns or use a group by on an index column. This works most of the times but still not 100% with list view threshold error encountered occasionally.
- The age-old desire of knowledge managers to have a flat structure and use metadata over folders can only be met if your library uses indexed columns as mentioned above. Be ready though as users may still report intermittent errors.
- Problem is mainly a data read error. If you filter your views to stay within the threshold limit then library will function normally, and users can add/edit data OK.
- If you build PowerApps on top of SharePoint large lists then be prepared to encounter the performance issues. You need to use delegation in PowerApps. However, if your app is popular and causes lot of traffic to SharePoint, it may get hit with throttling. For better performance, consider something other than SharePoint like SQL.
Workaround that work
- If requirements is to report from a large library, use PowerBI
- If requirement is to sort, filter a large library then use Search. You can build a pretty solid functioning UI using PnP Search web parts.
