How to get from a large list with Throttling enabled?

Question

We have a SharePoint list containing 50,000 items, and you want to get some data from it without disabling the default setting of SP2010.

From MSDN for processing large lists, we determined that the key ingredient would be to use a small RowLimit in SPQuery and use ListItemCollectionPosition for batch processing.

However, with our code (something) like this, a throttling exception still fires:

SPQuery query = new SPQuery(); query.Query = "<Where><Contains><FieldRef Name=\"MatterName\" /><Value Type=\"Text\">7476922</Value></Contains></Where>"; query.ViewFields = "<FieldRef Name=\"MatterName\"/>"; query.RowLimit = 10; int index = 0; do { SPListItemCollection batch = mattersList.GetItems( query ); query.ListItemCollectionPosition = batch.ListItemCollectionPosition; } while ( query.ListItemCollectionPosition != null ); 

According to MVP experts in SharePoint Connections 2010, this is by design, since implicit sorting by the result set will still cause a throttling threshold of 5000.

Which is good and all, but then how do we get from this list? Would ContentIterator use a better option? If so, what is the magic that the content iterator will use to make this happen?

Answer 1

You can use:

query.QueryThrottleMode = SPQueryThrottleOption.Override;

by executing the request as superuser.

http://adicodes.com/sharepoint-2010-list-throtelling/

Answer 2

As an administrator, not a developer, I don’t have a code solution for you, but you have 2 "no code" solutions for you.

• SP allows you to use a different set of throttling rules for the owner of the list / site collection - by default, I assume that it is set to 10000, but this may come across. The idea is that the average end user is throttled, but not the list owner. This may be helpful.
• SP also allows the administrator to determine the time of day when requests can be executed without any throttling. Therefore, if it is possible to run your queries at midnight, etc. - it may be an option.

Both of these settings are configured at the web application level.

Answer 3

Fields used inside your <Where> clause must be indexed.

The adjustment of indexed fields should also occur outside of throttling. For example, you have a new list to be indexed. However, as soon as the number of list items exceeds the throttling threshold, setting new indexes will fail, since throttling also applies to adding indexes.

Answer 4

You can use ContentInterator , which helps to access more than 5000 items in a large list without pushing the list restriction limit and getting a SPQueryThrottleException.

ContentIterator implements a callback template to segment a request to process one item at a time. Consider using this feature if you need to handle a large number of elements that may exceed the throttling limit.

Answer 5

there is an exception to the ContentIterator solution: if your index is indexed (which is required), if the index has more than 5000 rows (based on the central administrator), you will still get a throttle exception even before the contentIterator instance starts to view the content.