3

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

  • 3
    Could you clarify the number of items? Do you mean three-hundred thousand or three million? I'm unfamiliar with commas separating two places (unless they're used with decimals) – John-M Jan 25 '16 at 16:20
  • My core Question was, If I set list settings Display Items in batches of the specified size, say “1000". It gives paging ability in "all items" view. So since I navigate through pages Or batches of 1000 items, View query will only request for 1000 items? Or it will query for all 300 thousand(300,000)? – user117048 Jan 26 '16 at 4:21
1

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.

  • 1
    You'll also need to index columns to be able to filter your views to display fewer than 5000 items. If you try to filter a large list on an unindexed column, the view will fail. – Erin L Jan 25 '16 at 16:12
  • My core Question was, If I set list settings Display Items in batches of the specified size, say “1000". It gives paging ability in "all items" view. So since I navigate through pages Or batches of 1000 items, View query will only request for 1000 items? Or it will query for all 300 thousand(300,000)? – user117048 Jan 26 '16 at 4:21
  • 1
    @user117048 The answer to this lies in the "Manage lists and libraries with many items" article: "Defining an item limit in a view does not have the same benefits as filtering by an indexed column." You need to setup an indexed column and display the items by some sort of category in order to get around the list view threshold issue – John-M Jan 26 '16 at 18:36
  • Thanks John will do the capacity testing based on your inputs. And will update my findings here. – user117048 Jan 27 '16 at 5:03
  • It's one of my biggest issues with SharePoint Online at the moment. We have a client with a list that's grown to 16k items so far - It's not really possible for us to create a ton of views and keep them happy - They want to be able to search the list etc. – Dan Harris Jan 28 '16 at 14:05
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.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.