How to Manage Large Lists in SharePoint 2013
We all faced it one or many times when we work SharePoint Lists.
“The number of items in this list exceeds the list view threshold”
- How do we solve it?
- Can SharePoint Manage lists with more than 5,000 items?
Yes
, SharePoint is able to manage the lists with thousands of items. but there is a catch! you need to plan your list in advance.
What is this List View Threshold?
- Most of the time we understand this concept in a wrong manner, then we think SharePoint does not support lists to have more than 5,000 items.
- SharePoint Allow us to keep thousands of items in a list.
- This list view Threshold is the value which SharePoint will display items in a single list view.
- When you try to display your list items in SharePoint List view without a filter, it will bring you all (maximum 5,000) the items in to the page.
- Once items are loaded, SharePoint will prepare the pages and display the data for you. (by default page size is 30)
- SharePoint does not use the page size to filter or display the data from Database level.
- If you set the item limit in per page in your view settings, it will use the number to display items but not the query items from the database.
- Once you have items more than threshold value and you do not keep a filter criteria, SharePoint will stop querying the list and give you the above error.
Mainly there are two ways to handle the situation
- Increase the Threshold value in Web Application (Not Recommenced)
- Configure the list to handle the thousands of items without changing the threshold value.
Solution
We can implement multiple things in SharePoint list.
- 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
Let’s identify each and every point in above list.
1. Create Multiple Folders and Store items within Folders
- Insted of storing items directly in the list, we can create multiple folders based on our categories and we can store the items inside the folders.
Advantages
- We can simply overcome the list view threshold value error because, our items will get distributed within multiple folders.
- We are likely to have less than 5,000 items within a one folder, then you will not face the threshold limit error.
- If you have 5,000 items in the folder without sub folders, again you will get the same error message.
Disadvantages
- The moment you create folders, you start getting very long URLs for the folders
- In case if you need to share link of a document or a list item you have to copy paste a link which is longer more than two lines in the page.
- NOTE: To avoid this you can type the url as list name/foldername/itemid or document name.
- but it will be time consuming when you have multiple sub folders
2. Create Indexed Columns and create Multiple Filters using Indexed Columns
This will be a longer process than creating folders and you have to plan it while you are creating the list. But finally it will give you better results.
- Understand the requirement of the list which you are going to create.
- What is the information or documents which you are going to store?
- Who are the audience of the list?
- How frequent you are going to display the items and to whom ?
- Do you need all the list items to show in one page or is it ok to display data in chunks?
- What are the column types you are going to have in the list?
- Now you are ready to create your list, you can follow below guidelines to implement your list.
- Create Multiple Indexed Columns
- Create Multiple filtered views using above indexed columns
- use Group By feature in views
To follow above, you have to execute below.
- Create Multiple Views to display information based on your audience and their requirements
- You have to keep below points in mind while you create views
- Each list view should filter maximum 2,000 to 3,000 items per view.
- Create Indexes to the columns which you are going to filter data.
- Create Group By Functionality in the list view
- Column which you are using to Group By should be a indexed column.
NOTE:
- You can have maximum 20 columns as indexed columns.
- Column types such as Date & Time, Choice, Lookup, Integer, Number will be best options to create indexes.
- If you create Indexes with Single Line Text or Multi line Text fields, it will not help you to achieve the above.
Disadvantages
- Take time to implement.
- If you make many indexes, it will slow down the performance of the list.
Implementing Information Policy Management Feature to purge data will be discussed in another article.
This is the information which I have collected with the time. everyone is welcome to have your opinion in comments.
Thank you very much in advance