Up to 30 million items can be contained in a list or library. Now, when many users query such a large list or library at the same time, performance can suffer, errors can occur, or, in the worst case, the server can crash.
To prevent this, there is a limit to the maximum number of items an operation on a list or library, such as a query, can process at one time: the list view threshold. This value is 5000 by default. (Items in the recycle bin are also considered when determining this threshold, so it makes sense to empty the recycle bin regularly.)
Here we want to show you ways to work with large lists and libraries in SharePoint without hitting list view threshold problems.
Filters and Views
The ability to work with filters or views allows large lists or libraries to be presented in such a way that unneeded items are not included in a query, thereby reducing the likelihood of hitting the list view threshold. By the way, views with many elements work better in the modern view than in the classic one.
When filtering or sorting, the columns used for sorting or filtering should be indexed, otherwise an error message may occur. Manual indexing via the list settings is possible. Indexed automatically when
- sorted or filtered by column in saved views
- sorted in modern view. This only works in lists with a maximum of 20,000 entries.
Some view options do not allow lists with more than 5000 items. Therefore, note the following points:
- Sort by one column only
- Don’t sort by people columns, lookup columns, or managed metadata columns
- Don’t add grand totals like count, sum, or mean
- Do not show more than 12 columns of the following type: person column, lookup column, or managed metadata
Note to administrators: Creating personal views that correctly use a column index is not easy for large lists and libraries. Therefore, it may make sense not to grant permission to manage personal views to all users. This prevents a user from creating a view that encompasses all elements, thereby degrading site performance.
If the total number of items in a list or library does not exceed 20,000, 20 columns can be indexed in SharePoint. An indexed column can be used to create a view that uses the indexed column as a filter.
folder
A large list or library can be split into multiple folders for faster work. Viewing all items in a list or library without folders is faster to get past the list view threshold. If individual folders contain more than 5000 items, i.e. exceed the threshold, a filtered view should be used at the same time.
When creating a folder, an internal index is created at the same time. Subfolders are counted as elements of a folder, but the entries in the subfolders are not.
By default, folder creation is possible in document libraries; not in lists, but can also be permitted here via the appropriate settings.
Moving an item from one folder to another using a command or drag-and-drop preserves the item’s metadata.
In contrast to libraries, it is not possible to automatically move the elements from one folder to another in the case of lists.
Offline synchronization
Editing large amounts of data offline and only synchronizing the changes saves SharePoint resources. The Microsoft Office products Access, Excel and Outlook are suitable for this, both as a desktop and as an Office 365 variant.
Microsoft Access can handle more rows of data than SharePoint. Under certain conditions, up to 50,000 entries can be processed. Access processes data from lists or libraries one at a time in small batches so that the list view threshold can be bypassed and SharePoint performance is not affected.
Microsoft Excel : SharePoint lists can be exported to an Excel spreadsheet.
Excel can also be synchronized with SharePoint and Access, combining the advantages of all three platforms. In addition, Excel offers a number of functions for data analysis (e.g. pivot tables or charts).
Updating the data in Excel from the SharePoint list overwrites the data in Excel with the most recent data from SharePoint. Again, up to 50,000 items can be processed using the Access Services feature.
Microsoft Outlook can be helpful when editing contact or task lists or synchronizing document libraries. Task lists can, for example, be edited offline (assigning new tasks) and then synchronized with SharePoint from Outlook. SharePoint contacts can also be stored and managed more efficiently in Outlook.
Document Center
A document center is a template that you can use to create a web page that provides specific functionality for working with documents, such as searching, saving, and editing. Designed for document management, it also provides an approach to working with large libraries by enabling workflows, filters, views, and file check-out and check-in. Working with metadata and content types, as well as tree view navigation, documents can be efficiently organized and accessed.
SharePoint search box
There are typically two search boxes on a SharePoint page: one for overall site search, at the top of the page, and one specifically for searching within each list or library. When searching with this latter field, you can successively expand the scope of the search: Normally, the search area refers to the current view including all subfolders. Search results are displayed in columns that can be filtered and sorted. If the list view threshold is exceeded, not all results will be displayed.
If you don’t find what you are looking for in the search result, you can expand the search scope to the entire list including all subfolders. Finally, you can extend the search scope to the entire site, regardless of the list view threshold. In that case you will see all the results on the default search page. With the help of the restriction area, you could then, for example, filter the search result according to the author of a document or the creation date of a list item. This SharePoint search uses its own indexing mechanisms and is not subject to the list view threshold.
Note : Although the search field is displayed by default, it can also be hidden by setting an appropriate property.
Relational lists and RSS feeds
Other ways to circumvent resource limitations could be relational lists or RSS feeds.
Relational lists can reach the list view threshold and become blocked under certain conditions. In particular, care should be taken when using lookup columns, unique columns, and referential integrity.
If RSS support is enabled in Central Administration and at the site collection level, you can also configure it for different types of lists and libraries. When users access the RSS feed for a list or library, data is retrieved from the list. The default RSS view limits the number of items returned based on the date the item was last edited and using a filter on the Modified column. If the list or library contains many items and users access the RSS feed, the Modified column should be indexed. You can also reduce the number of items displayed by changing the “Number of items” and “Number of days” for which changes are included in an RSS feed.
Notes for Administrators
As an administrator for SharePoint servers running on-premises or on Azure, you can temporarily or permanently override the list view threshold. (These two options do not exist for SharePoint Online!)
For example, it makes sense to set a specific time window in which users can perform operations without data limits. Ideally, this should take place during a period when only a few users are active overall, so that no one is affected by the degraded performance caused by extensive operations. Activities that should take place in such a time window, in addition to creating views that include all elements of a list or library, are the following:
- Creating and deleting an index
- Add and edit a list column
- Delete and copy folders
- Change security settings for a list or library
- Saving a list and its contents as a template
- Deleting a large list
- Deleting a site
- Restore or delete items from the recycle bin
It is not advisable to permanently increase the data limit, since the processing of very large amounts of data in a list or library can lead to severe performance losses, errors or the server crashing.