Issue: 

If you get error “The number of items in this list exceeds the list view threshold, which is 5000 items” on SharePoint Online list or any SharePoint on Premises environment.

Let us first understand why does SharePoint have a list view threshold and how does it help you besides throwing an error. Always when dealing with large amount of items takes time and resources, SharePoint provides methods to help you perform common operations efficiently such as using indexes to filter and metadata navigation fallback to return a smaller set of results when a request is too large.

In couple of operations such poor performing queries (Query is set of command which executes on SQL server to get everything from a list or filter without indexes) or operations that affect every list item such as creating a new column take time. Small list generally does not matter because there are so few items that the operation is fast. As and when the size of list increases, operations take longer time and use more resources. Just before such operations run unbounded\uncontrolled, the list view threshold prevents them. You can think of the list view threshold as a check engine light letting you know that you should change the query and how data is accessed or perform the operation when farm usage is low.

Why did Microsoft set the the limit to just 5,000 is another question which comes to our mind. When any user perform any operations on SharePoint site (against a database), SQL Server locks rows to prevent conflicts from concurrent operations. By default when operations start affecting more than 5,000 items the entire table gets locked because it is more efficient than locking the individual rows. SharePoint stores all list items for an entire content database in a single table.

Now lets go more deep into the problem, here when queries which effect approximately more than 5,000 items (even if it is a read) that table will be locked and other operations\queries\transaction for all lists in that content database wait until the current operation\query is completed. Default value of these 5,000 was selected by Microsoft after lot of recommendation \ testing and performance tuning. Decreasing this limit will result in some performance improvements, and raising it even to 10,000 will decrease throughput if operations are commonly performed that would otherwise be prevented.

Office 365 \ SharePoint Online
*******************************

Note: Because this is hosted environment, you cannot Increase\change this limit in Office 365.

Workaround:

Only workaround for issue is to split the items into multiple list. so that no list exceeds 5,000 items. You will never be able to view this list items beyond the limit of 5,000 items.

Incase you are are planning to delete some items from the list but you can’t do so because you’ll be unable to view the list items beyond the limit of 5,000 items, you must configure metadata-based navigation so that you can access all the list’s contents.

To enable metadata-based navigation, follow the “Enable Metadata Navigation and Filtering” and “Configure Metadata Navigation”

After metadata navigation is enabled, the list’s contents will become available. We recommended that you then reduce the list’s contents to less than 5,000 items.

 

SharePoint 2010\ SharePoint 2013 \ SharePoint 2007 on Premises
***************************************************************

Here are couple of solution to this issue.

• Accessing list views that contain too many items
• Attempting to perform privileged operations (adding a column, creating an index, adding content types)
•  Custom code that accesses a large list

Accessing list views that contain too many items:

Basically, you need to add an index on the filtered column. Open SharePoint list and click on “Modify this view” in the ribbon. Under the “Filter” section, you should be able to see which columns are being used for the filter. Then, you can simply navigate to your list settings, select “indexed columns” and then “Create a new index” for each filtered column, and you’re good to go!

You can also configure a views to filter on indexed columns or move items into folders so there are less than 5,000 items in the root of any one folder or the root of a list. One good example for same is to use it in “My Documents” view. This is an easy view to configure because it uses system metadata that will always be set for every item. In this view you filter by Modified By and/or the Created By columns. To create this view in the filters section of the settings for a view select the Modified By column and set the value to and then set a second filter with OR on the Created By column with the value. Created By column should be used as well as Modified By when multiple users edit the same documents. Modified By is not a multiple user column so this view will not necessarily show all of the documents a user has ever modified. In this example both columns should be indexed because it is an OR operation

Attempting to perform privileged operations (adding a column, creating an index, adding content types):

Another way to mitigate this operation is to configure a daily time window so privileged operations can be performed when the farm has low usage. A specific time window can be configured for every web application in Central Administration. If any user receive a list view exception they will be notified so that they can perform the operation during the privileged time window. This will help users to perform operations such as adding columns \ content types to large lists and you get to control the time frame when users can perform these operations.

Custom code that accesses a large list

Normally in custom code there are couple of general guidelines to follow so that you can index to filter SPQueries to amounts less than the list view threshold. Couple of example are if you are planning to use an SPQuery to find a specific item in a list based on a particular column make sure it is indexed. There are always this possibility that the SPQuery may return more results than the list view then the content iterator class should be used to work through the content without hitting the list view threshold. It is especially important you are doing some operations on all the contents in a list or all of the content in a site or site collection.

Below is a example to perform a SPQuery to get all items in a list based on a non-indexed field. This is a bad idea on large list and this query should not even be considered 🙂

######Query 1########
SPQuery query = new SPQuery();
query.Query = “<Where><Eq><FieldRef Name=\”MyUnindexedField\”/><Value Type=\”Text\”>FieldValue</Value></Eq></Where>”;
SPListItemCollection items = list.GetItems(query);
// Do what you need with the collection of items
######Query 1########

Not just this query is on a unindexed field, it is also not limiting the results. So on a large list, this query will be blocked because the field is not indexed and too many results may be returned.

If you can add an index on MyUnindexedField, then you can use NVP (Name Value Pair) paging on that index:

######Query 2########
SPQuery query = new SPQuery();
query.Query = “<Where><Eq><FieldRef Name=\”MyIndexedField\”/><Value Type=\”Text\”>FieldValue</Value></Eq></Where>” + ContentIterator.ItemEnumerationOrderByNVPField;

ContentIterator ci = new ContentIterator();
ci.ProcessItemsInList(query,
delegate(SPListItem item)
{
// Do what you need with each item
…
},
delegate(SPListItem item, Exception e)
{
// Handle an exception that was thrown while iterating
…

// return true for ContentIterator to rethrow the exception
return true;
});

######Query 2########

You can always use the Office 365 workaround where SharePoint will automatically help you create the indexes for you. You can do this by enabling Metadata Navigation and Filtering as a site feature and enabling it on the lists and libraries you think will eventually reach the view threshold.
Finally, it is impossible to cover this topic without going over this last possibility which is to increase the value of the List View Threshold in the SharePoint Central Administration in order to allow the migration of your list/library with more than 5000 items. I would not venture into this option without first understanding that it might have an impact on the Server performance. In sum, this is not a recommended action, but it can still allow you to migrate your list without too much trouble.
Reference:
http://support.microsoft.com/kb/2759051

http://en.share-gate.com/blog/how-to-deal-with-the-5000-items-list-view-threshold

http://blogs.msdn.com/b/dinaayoub/archive/2010/04/22/sharepoint-2010-how-to-change-the-list-view-threshold.aspx

Comments are closed.