Large Lists In SharePoint Online

Managing large lists in SharePoint Online is not dissimilar to doing the same for an on-premise SharePoint environment; the difference being that Microsoft has set in stone some settings that can be modified when you have access to the SharePoint Administration Site and the SharePoint servers in your farm.

While SharePoint 2010 introduced the ability for lists an libraries to contain millions of items it also introduced a number of strategies for Administrators to use to protect the integrity of their SharePoint Farms.

The most obvious of these configurable tools from a User perspective is the List View Threshold; by default it limits the number of list (or library) items that a database operation (e.g. query) can process. Other operations are also prohibited on lists larger than this threshold such as

• Adding, removing or updating a list column
• Adding, removing or updating a list content type
• Creating or Removing an Index
• Managing files that have no checked in version
• Non-indexed recursive queries
• Cross list query
• Lookup columns that enforce relationship behavior
• Deleting a list
• Deleting a site
• Saving the list as template with data
• Displaying totals in list views
• Enabling or disabling attachments in a list
• Explorer view (see more detail below)
• List and folder permissions

Because of the multi-tenant nature of SharePoint Online this value is fixed at 5000 items and so presents some challenges. Once a list contains more than this number of items a message is displayed on the list settings page alerting you to the fact that functionality has been degraded.

NOTE: this statement does not imply I advocate increasing this or other default setting in an on-premise environment without first evaluating the cost and impact in terms of performance and usability.

Techniques for Managing Large Lists

Use an index for organizing large lists; allowing items to be filtered up to the 5000 item restriction and will improve query times, however it may only display the newest list items but in such a case a message will be displayed on the list view page.

Another way to accommodate large list items is to employ folders to silo information. However there are some restrictions here as well and folders, in my opinion, are generally to be avoided in SharePoint.

Additionally you may find respite in using the Data Sheet View; in this view the threshold for items in a list is 50,100 but again there are limitations in that this requires 32-bit Office 2003 or later and only works correctly in the 32-bit version of Internet Explorer.

SharePoint Workspace may also be a solution as this allows up to 30,000 items to be synced (but not calendars and some other lists) and after the initial sync is complete only changes to the list are transmitted to maintain performance.

Other options include Excel (up to 52,000 items) but work is all client side and the sync is one way or Access that will allow two way sync.

Metadata navigation may also be employed to help users find content in large lists and libraries by using a navigation hierarchy tree control to apply filters to a view. Key filters can be used in combination with the navigation hierarchy to refine the number of items displayed. More on this in another post.