There have been several past posts that deal with the list threshold, such as here. A list may exceed the list view threshold and then some operations will be blocked. The big problem with this is that the default list view can’t be used to access the list, bad news bear! They have to be properly configured before they can work with a large list. The list view threshold blocks the database operations that affects more items than that threshold allows. It won’t just affect the number of items that have been returned.
There are two classifications that come into the picture when you have large lists: “List Exceeds The List Value Threshold” and “Container Exceeds The List View Threshold”.
There are operations that can be blocked when the size of the entire list exceeds the list view threshold. This occurs even if the items are placed into folders. These operations include managing and checking versions, operations of all items, and recursive queries. The views that return all items without folders can also be prevented. There are operations that affect a complete list too including adding a column or deleting indexes and they can be blocked.
There can be operations prevented due to the folder for the list containing more items than the list view threshold allows. You won’t be able to rename it or to delete it so you do need to be careful. The list view threshold can prevent you from performing some common actions when you setup your list. This is why you should configure the columns and indexes for a list before the size is greater than the list view threshold.
Should a list exceed what the list view threshold allows, then you need to plan to configure it correctly. You need to configure view and navigational options well in advance. However, lists can grow beyond the list view threshold and that will require some action from you. For example when you create a column or index that column in a list you need to be prepared for it to take time. The operations are prevented by the live view threshold. They can be performed during the daily time window. They can also be performed by the farm and computer administrators.
The operations need to be planned well in advance. The list may be too big so you will need to use a daily time window. An administrator with the right privileges may be needed in order to perform the necessary operations. It is possible for a list to become so large that some of the operations can time out when they are used in associated with a Web browser.
List Exceeds The List Value Threshold
- Add/Remove/Update a list column All of the columns including lookup and calculated columns. There are updates such as name change. They aren’t blocked due to the fact that they won’t affect all of the items in the list.
- Add/Remove/Update a list content type Every item in the list is affected so it is blocked if the list has more items than the list view threshold.
- Create/Remove indexes it is blocked for any list that has more items than the list view threshold has it affects each item in the list.
- Mange files The non indexed query fails for any list that has more items than the list view threshold.
- Non indexed queries This includes filters and various sorts. The operation will fail if the list size is larger than the list view threshold. There isn’t an index so a full scan of the list occurs. The items will all be returned but the folders will be ignored.
- Cross list query This includes the various queries by the Content Query Web Part. It follows the list view threshold setting for auditors and administration. The default for it is 20,000. For operations above that threshold it will fail.
- Lookup columns This refers to those that enforce relationship behavior. You can’t offer lookup columns like this though if the list references content for more items than the list view threshold.
- Delete a list This is blocked if the list has more items than the list view threshold due to the fact that it affects every item in the list.
- Delete a site This affects all of the items in the list so it’s blocked for any list if there are more items in it than the list view threshold.
- Save the list as a template This affects all of the items in the list so it is blocked for any list with more items than the list view threshold.
- Show totals in list views This performs a query against all of the items in the list. It is blocked for a list that has more items than the list view threshold.
- Enable/Disable attachments for a list This affects all of the items in the list so it will be blocked when the list offers more items than the list view threshold.
Container Exceeds The List View Threshold
- Delete/Rename/Copy a folder This fails if the folder contains more items than the list view threshold as too many of the rows will be affected.
- Queries to filter non indexed columns This fails if the folder or list has more items than the list view threshold. It performs a full scan against the entire folder because there isn’t an index.
- Fine grain security permissions This fails when the list or the folder that is being set has fine grained permissions that contain more items than the list view threshold as too many rows are affected. You can use the fine grain permissions on documents in a large list. However, you can’t set the permissions of the list or of the folders if they contain more than the list view threshold.
- Open with Explorer This won’t show any items if a container has more items than the list view threshold, other than in reference to sub folders. If the root list contains more items than the list view threshold the “Open with Explorer” won’t show anything. In order to use Open with Explorer the list needs items to be organized into folders. The amount needs to be less than the list view threshold in the root for a given container.
List views offer several benefits over previously discussed approaches in this series. Lists views provide a heightened experience through dynamic sorting and filtering while being simple to customize the views and to display various columns. The problem with views is they tend to be very expensive in terms of performance with the slowest result time.
Both list views and metadata navigation offer support for content retrieval when it comes to large document libraries, folders, and indexes. When you query with a list view in real time and queries with the SQL server database you can get recent results and decent performance. The throughput will decrease with large list size. List views also allow for the most recent content loaded to show up on page.
Metadata navigation and list views can be used in order to increase the ability to perform list view actions for an item. List views are the primary method for working with a list in low read scenarios. When there are many read operations in place you need to consider the other query methods and use them to help you access the list data.
There are many things to look at for the topic of view configuration. Choose carefully the columns that will be used in views. When you add more columns you have more data available. However, this makes the page load time longer so that is the trade off you have to make. Finding the balance that works well for you here is very important. Reduce the number of lookup columns in order to manage the metadata for people and groups. They can be connected with other database tables. Avoid using totals for columns. If you don’t plan to use indexed columns for filtering then you need to have the items show in folders. This ensures that the folders will never have more items in them than the list view threshold. The views need to be filtered on indexed columns to help reduce the number of items returned. This is especially true if there aren’t any sub folders to push more items into than the limit for the list view threshold. Allow the metadata navigational feature to offer the most recent results for queries. They would otherwise be subjected only to the list view threshold. This is usually going to be enabled for you on all the sites. When filtering views with metadata navigation you should consider using per location views. They create unfiltered views for metadata navigation pivots.
Views are typically used to help access list data. Views have to be selected carefully though to optimize the way in which a user is able to find content and to meet the requirements of policies for performance. With a list that is large in size, it is important to carefully decide how to configure it. The standard views should be used. Avoid Datasheet, Calendar, and Gantt views as they can cause you to exceed the list view threshold.
The views should always have the least number of columns as you can possibly arrange. Pay close attention to the number of lookup columns though. They have to be able to join other tables or your performance suffers.
With SharePoint Server 2010, as discussed in previous posts, the new list view threshold significantly changes the approach for how to use view with a large list. Users may get errors if they attempt to return more results than the list view threshold allows. The use of totals on the large list will be blocked when that occurs. The number of items have to be scanned and that is what is important versus the number of rows that are returned.
When a list is organized a folder shouldn’t contain more items than the list view threshold. When you show all items outside of folders you should avoid the mechanics being in place where they can filter the results.
The recently changed items view is used for a user to identify what has changed. It can be set at the default when a user frequently accesses various types of content from the items in the list. This view is simple enough to configure as it relies on system metadata that is set for each of the items. When you have a large list then you either need to set it for the item limit of filter the results to show less than the list view threshold. This view is created when you index the modified field. Then you sort it in descending order. The my items view can be used with repositories where users access the certain documents frequently. This is simple to configure as it uses the system metadata that is set for each time. You can filter it with the modified by or created by columns. This is a view in the filters that can be set for a multiple user columns. It won’t show all of the documents in this view but the most recent.
There are some general recommendations to consider when it comes to unique permissions in SharePoint 2010. They include:
- Minimizing the use of unique permission on individual items. They will simplify list design to require more items for unique permissions.
- When unique permissions are necessary, set them only at the list or folder level. Minimize the number of individual items that you need unique permissions for.
- Reconsider your design for each item required with individual permissions. It may be a good idea to divide items between multiple lists so that they can be organized with other items into groups and folders. The proper access needs to be authorized for that unique permissions can be allowed on each item.
Granular permissions can affect performance and they are also very hard to manage. Therefore, you should leave them set at the defaults, you certainly don’t want to set them to where the list view threshold is exceeded. If you do so they will be blocked due to too many individual items being updated at the same time. Setting granular permissions can affect performance in many other ways too. The result is that there is a configuration limit by default to 50,000 unique permissions for each of the lists.
When you try to declare unique permissions after you reach that limit, you will be blocked from doing it. The list view threshold doesn’t have that block in place and it will allow you to continue to create unique permissions for each item but not for a query. Permissions can be inherited but also broken for the items when they are in a folder. There they will be considered one unique permission.
Every time a permission inheritance is broken then a new scope ID is developed. When you query on a view you can JOIN the scope table for that query. There is then unique access control over the list that has to be processed. When there are many unique permissions in a list then it can reduce the overall performance of the query so that isn’t recommended. The number of unique permission in a list gets bigger over time and that reduces the performance. While the limit is by default 50,000 it is ideal if you make your customized limit 5,000.