SharePoint list throttling limits to access maximum 5000 items through view/query. However your list can hold as much as 30,000,000 items but you can’t access more than 5000 items at once by view or query. But I’ve seen scenarios where list items goes millions in a year. So accessing large list having more than 5000 items not very rare in real life.

The real limit of 5000 items is related to SQL server. I’m not a SQL server expert but let me try to explain as a non-SQL server expert – “If a SQL query returns more than 5000 items, SQL server locks the entire table – which will affect other queries on the table”.

So as a SharePoint expert you might need to be in a situation where you need to work with large list (having more than 5000 items). However, there’s few options you have when you need to access large list. I’ll explain different options with pros and cons.

Option 1: Access less than 5000 items Iteratively
With this approach,  you loop through and access less than 5000 items on each iteration (say, 2000 items per iteration). The following block shows the idea:
The above approach is a good option for querying items through SPQuery. However, for list view, you need to modify your views to make sure the view doesn’t return more than 500 items.
Points to note:

  • You need to modify your existing views so that they don’t return more than 5000 items.
  • Once you modify your list views (as well as change the code to access less than 5000 items), the queries/views will have less affect on other live users.
  • However, you need to modify your existing code (or new code) to use the pattern to access not more than 5000 items.
  • Also for each loop, you will send a SQL query that will return the items.

Option 2: Use ContentIterator

SharePoint Server provides an API to access individual items using ‘ContentIterator’. ContentIterator is kind of complete API for processing large list items.
Points to note:

  • The API has full capabilities of handling different scenarios
  • And the API is available in Office Server version not in Foundation

About The Author

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">