How to fetch from a large list with Throttling enabled?

Question

We have a SharePoint list containing 50.000 items, and want to fetch some data from it without disabling SP2010's default throttling.

From the MSDN artical on handling large lists, we figured the key ingredient would be using a small RowLimit on the SPQuery, and using ListItemCollectionPosition for batching.

However, with our code (something) like this, the throttling exceptions are still triggered:

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 the MVP experts at SharePoint Connections 2010, this is by design, as the implicit sort on the resultset would still trigger the 5000 item throttling threshold.

Which is nice and all, but then how do we fetch from this list? Would using a ContentIterator be a better option? If so, what is the magic the content iterator would pull off to make this happen?

score 3 · Answer 1 · answered Nov 10 '13 at 14:29

3

You can use:

query.QueryThrottleMode = SPQueryThrottleOption.Override;

by executing the query as a super user.

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

answered Nov 10 '13 at 14:29

carraua

1,398
17
36

score 2 · Answer 2 · answered May 27 '12 at 09:28

You can use ContentInterator which help with accessing more than 5,000 items in a large list without hitting a list throttling limit and receiving an SPQueryThrottleException.

ContentIterator implements a callback pattern for segmenting the query for processing a single item at a time. Consider using this capability if you need to process a large number of items that may exceed a throttling limit

score 2 · Answer 3 · answered May 01 '13 at 16:19

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

score 2 · Answer 4 · answered Dec 18 '10 at 14:32

As an admin, not a developer, I don't have a code solution for you - but do have 2 no-code "solutions" for you to consider.

SP allows for a different set of throttling rules for the list / site collection owner - the default I believe is set at 10000 - but that can be bumped up. The idea being that the average end user is throttled, but not the list owner. That might be helpful.
SP also allows for the admin to define times of the day when queries can be executed without any type of throttling. So if its possible to run your queries at midnight etc - that might be an option.

Both these settings are adjusted at the Web Application level

score 2 · Answer 5 · answered Feb 07 '12 at 22:31

Fields used inside your <Where> clause would need to be indexed.

Setting indexed fields needs to happen outside of throttling, as well. For example, you have a brand new list, setting which columns are indexed will pass. However, once that list's item count goes above the throttling threshold, setting new indices will fail, since throttling applies to adding indices as well.

How to fetch from a large list with Throttling enabled?

5 Answers5