SharePoint can now support very large lists but there are limitations on the number of items that can be returned in a view. The default limit with SharePoint 2007 was 2000 items and this has been increased to 5000 items with 2010, however this can be changed by an administrator. SharePoint has been criticised for this but personally I can’t see why. Many leading ECM products have search results throttling and some are even as low as 500 items by default. Increasing this value to thousands of items often creates serious performance problems with these products.
With SharePoint, if a query exceeds the resource throttle or limit that has been set by the administrator then you may be prevented from running it. This is because accessing that many items could have a negative impact on the site and cause problems for other users.
Daily Time Window
Administrators can set up a daily time window, usually during the evening or very early in the morning, when throttling is relaxed and users can run unlimited operations.
Throttles/Limits for Lists and Libraries
- Items: 50 million items per list or library limit
- File Size: 2BG file limit or item attachment limit
- List View Threshold: 5000 items default value. When a operation will return more than 5000 items throttling will occur
- Unique Permissions: 50,000 available per list or library
- List View Lookup Threshold: 8 the maximum number of join operations that can be performed
- List View Threshold Size: 20,000 items can be retrieved at one time when performed by an administrator or auditor with appropriate permissions.
- Allow Object Model Override: Default is Yes. This allows developers to perform operations and queries that can return more than the 20,000 result limit that is usually in place.
- Daily Time Window: Default is none. Specifies a time period where limits are ignored.
Why 5000 items?
SQL Server typically uses row level locking when performing updates and selections against a table in order to minimize database contention. This ensures that the process accessing the data rows has full control of the data in order to carry out updates etc. This is far more efficient than locking the whole table as this could affect the performance for other users who are accessing other rows. Saying this however, when very large updates are run that affect more than 5000 records then SQL Server will typically lock the entire table as it is more efficient. This prevents other users from accessing the table and can become a serious problem if it happens regularly. Note that the default of 5000 is an arbitrary value defined by Microsoft as it is not a fixed value and can change from site to site. Thresholds and limits are essential to help minimize the impact of resource-intensive database operations and balance the needs of all users.
So What’s the Answer?
- Create Indexed Columns – You can index up to 20 columns per list but in reality you shouldn’t need to index more than 2 or 3 as each one takes up resources.
- Create Intelligent Views – Create views that return subsets of information i.e. less than 5000 rows. Do this by filtering on specific values; using indexed values will improve performance but only one index is actually utilised by a filter so make sure that this is applied to the first/key column for the view.
- Specify Item Limits – this will return results in a paged fashion e.g. 25 items per page. This will retrieve results in batches rather than in one go
- Limit the total number of items returned – for example this could return the first 4000 results; the problem here is that a user won’t know that they are not seeing the complete result set and this can cause problems.
- Use Folders – Folders are more intelligent in 2010 and limits can be applied e.g.5000 items. The Content Organiser can be used to automatically create new folders as and when they are needed.
- Access Services – can be used to display unlimited items from a SharePoint list.
- Metadata Driven Navigation – can be used to enhance and speed up the way that users work with large result sets.