SharePoint online large list with 3,00,000 items preventing list throttle

Question

In SharePoint online (2013) list will grow up to 3,00,000, currently we only have 100 items. And the default list view throttle in SharePoint Online is set to 5000 limit. By default, If we visit the list, it will open in Allitems.aspx view. So if we go to “All Items view” and set the “Item Limit”, Display Items in batches of the specified size say “1000” then users can navigate the list items in pages, So will this prevent encountering the List view throttle limits?

In other words how can I ensure users to navigate lists with over 3,00,000 items without encountering the throttle limit?

Because my understanding is that SQL will issue a lock, if view generates a query exceeding 5000 items. So by setting “Display items in batches of the specified size say 1000, view will only query for those 1000 items per page. But one of my colleague says even with that setting we will encounter “throttle limits” and navigation to the list will be blocked? If So how to prevent list throttling and enable users to navigate the list items in a large list?

Moving list items to a different list is not an option for us.

Thanks

Answer 1

An unfiltered, unsorted, paged html view on a large list works just fine in SharePoint 2013 (I have worked with dozens of large lists in SharePoint 2010 and 2013, some with over 1 million items) provided you set the Item Limit for the view to a number less than 5001, because the view only queries one page of results at a time. It will therefore will also work in SharePoint Online, as the List View Threshold (LVT) operates the same way in SPO as in SP13, with ONE exception, noted below.

Provided the view renders without throwing a LVT error, the Search box will work just fine to find items that meet a certain criteria, because the Search box uses the Search index, which is not subject to the LVT. Caution: there may be a time lag between the time an item is added to the list or edited before search will show it.

Indexing difference between SP13 and SPO In SP13, you must index a column that you want indexed BEFORE the list reaches 5,001 items OR during a Daily Time Window for Large Queries. (Note: Many SP13 farm admins refuse to provide a Daily Time Window). In SPO, there is no Daily Time window, but you can index a column on a list provided the list has no more than 100,000 items on it.

Gotchas: Adding a sort on an unindexed column to an unfiltered view in a large list DOES run afoul of the LVT, because the view now has to query the entire list in order to sort it. It may help to keep in mind that the ID column is ALWAYS indexed automatically by SharePoint, so a sort on ID will not violate the LVT.

Answer 2

Provided you don't exceed the list view Threshold (5,000 items*) you should be okay up to the max number of supported list items (30,000,000**)

See the TechNet article "Software boundaries and limits for SharePoint 2013" for more information.

---

*: The list view threshold is defined as:

[...] the maximum number of list or library items that a database operation, such as a query, can process at the same time outside the daily time window set by the administrator during which queries are unrestricted.

And is typically related back to the number of items you attempt to display at once using a particular list view; make sure your views, especially your default view, only attempt to show a subset of the total items in large lists. See the Office Support article "Manage lists and libraries with many items" for more info.

---

**: The 30,000,000 limit on the items per list also has some additional information:

You can create very large lists using standard views, site hierarchies, and metadata navigation. This value may vary depending on the number of columns in the list and the usage of the list.

You can find more information on how to do this in the Office Support article "Manage lists and libraries with many items" listed above.

Answer 3

If you want to use the "built-in" SharePoint view functionality, you'll need to set your views (including "AllItems") to return a maximum of [ListViewThreshhold : 5000] using indexed columns. This is the count after filters are applied and before the paging is applied. As soon as you hit 5001, most users won't be able to load the view at all, even with default list paging.

If it's not realistic to chunk things out like this (which is typical given that 5000 is such a low number), then you should consider writing a custom app part. This will let you show "All Items" in a single "View", using server side paging that you control.

Obviously, this kinda sucks and defeats the simplicity SharePoint is known and loved for. It's one of the big gotcha's with SharePoint. It's great for demos and prototyping, but it handles "real-world" scenarios pretty poorly.

Answer 4

Managing Large list will be a challenging activity within SharePoint. But I believe it is achievable at least up to a good extent

Solution We can implement multiple things in SharePoint list

1. Create Multiple Folders and store items within folders Create Indexed Columns and create Multiple Filters using Indexed Columns Purge List data periodically ( based on the requirements) Delete data periodically Implement Information Policy Management in SharePoint

I have discussed same in detail level in how-to-manage-large-lists-in-sharepoint

in Blog Get-SPNote