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?

Answer 1

You can use:

query.QueryThrottleMode = SPQueryThrottleOption.Override;

by executing the query as a super user.

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

Answer 2

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.

1. 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.
2. 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