I was asked to explain list throttling in SharePoint 2010 to a client today, and thought it might be valuable to share a chunk of the email for future use;
- Adding indexes to columns will not defeat the throttling limits for standard users.
- Adding indexes to columns will allow views to be constructed that filter the result set to avoid throttle limits.
We can therefore state the following;
- If you try to sort, or filter a list on an unindexed column, the throttling rules are applied before the result set is compiled.
- If you try to sort, or filter a list on an indexed column, throttling rules are applied after the result set is compiled – so filters and sorting will work, but you still need to constrain the number of items to fall under the throttle limit.
- There is an exception – the built in ID field doesn’t cause throttle limits to be applied when used to sort.
Therefore, if you want to be able to sort the views of a list with more than 5000 items (the default SP 2010 throttle limit), you need to constrain the number of items by applying a filter on an indexed field. Hopefully this explains the issues you are seeing – basically that indexing a column does not mean you can return more than the throttle limit when sorting on it.
Also, remember to test as a normal user – administrators are not throttled.
I’ve been investigating methods to clear thousands of versions from a few documents in a client SharePoint system recently (caused by an errant workflow that nobody spotted). The client was able to delete the documents – sending them to the recycle bin, but could not empty the recycle bin – SharePoint timed out and fell over each time.
The solution we ended up looking at involved emptying the versions from the document, and it taught us a number of lessons;
- When dealing with documents, you must always deal with the SPFile object. The SPFile and SPListItem objects both have version histories, but they are subtly different (as explained below).
- SPFile.Versions is a collection of all the versions except the latest version. The collection is stored in reverse order.
- SPListItem.Versions is a collection of all the versions including the latest version. The collection is stored in sequential order.
- After removing an SPFileVersion from the SPFile.Versions collection, you should force an SPFile.Item.SystemUpdate() – to ensure the SharePoint interface reflects what you have done (we stumbled across this quite by accident – we had the API reporting it had completed it’s work, and the web interface reporting the versions still existed).
- If you are going to delete a lot of versions sequentially, make sure you put a delay in the loop to give SharePoint a chance to breath – many operations in SharePoint are asynchronous, and can be dropped if the system runs out of threads.
I have to admit I’m still a little mystified at some of the stranger facets of the whole SPListItem/SPFile relationship – such as the SPFile object not having a full complement of properties under certain circumstances. Very odd.