How to overcome threshold frequency in SharePoint List

Question

We are looking for how to overcome threshold frequency in SharePoint List.

Thanks,

Answer 1

You need to utilize indexes and make sure to never request to many items.

Using folders is also a way to use indexes indirectly.

In code you can bypass the issue by using paging to iterate over the items, requesting up to 5000 items per batch (edit: Must be sorted by an indexed field).

[edit] Clarification on the 5000 (default) item limit. The exception will be triggered once SharePoint is "forced" to load 5000+ items. The internal iterator will throw this exception once SharePoint retrieved 5001st item from the SQL DB. Even if SharePoint would only need to display 30 items, this can still happen if there is an additional sort or grouping involved. So if having large lists try to avoid any additional sorting or grouping if you have a filter. Make sure your query (incl the internal order for paging) can be satisfied with only one indexed field (or several fields that belong to the same composite index)

Additional word of warning: Once the limit is breached, some operations that affect all items in the list wont be possible on the list anymore, among them is the creation of an index!!!

Answer 2

If you want to achieve this using JSOM, then please refer below post answer by me.

Error : it exceeds the list view threshold enforced by the administrator

Answer 3

I take it you mean the List View Threshold (LVT) of 5000 items introduced with SharePoint 2010 and valid in that form up to and including SharePoint 2016.

If you query a list as normal user (not farm admin) within a web application with default resource throttling configuration you will get no results at all, if the list or a referenced list via a lookup column has more than 5,000 items. There a couple of options to work-around this issue, so that you can push the limit to something like 20 - 30,000 in my experience.

• Add an index to the list and do not query for more than 5,000 items at a time
• Disable the threshold completely for a specific list by code
• Increase the threshold limit for the entire web application via Central Administration (all the time or in certain time windows)

Simply limiting your query (without using an index), e.g. by using a paged list view or RowLimit in a CAML query, does NOT solve the problem.

Indexes can be created in multiple ways, here are a some options:

• Add an index manually via the list settings or by code
• Activate the Metadata Navigation for the list (requires Publishing feature activated though) - automatically creates an index
• Use folders (also possible in lists)

The same solution works for dependent lists that are referenced via a lookup column.

I think that the ultimate resource on this topic is this article: https://support.office.com/en-us/article/manage-large-lists-and-libraries-in-sharepoint-b8588dae-9387-48c2-9248-c24122f07c59

If you want to disable the threshold for a specific list have a look here: Disable Throttling on a single list

By the way - the same applies to document libraries.

Finally, the best way to work-around the issue, in my opinion, is to use indexes. Then you do not compromise the overall system/farm performance and it is typically not to difficult to determine which columns make good indexes.