Get items from a list that has more items than the List View Threshold

Question

I'm using SharePoints object model and I'm trying to get all or a subset of the items from a SharePoint 2010 list which has many more items than the list view threshold (20,000+) using the SPList.GetItems() method. However no matter what I do the SPQueryThrottledException always seems to be thrown and I get no items back.

I'm sorting based on the ID field, so it is indexed. I've tried setting the RowLimit property on the SPQuery object(had no effect). I tried specifying the RowLimit in the SPQuerys ViewXml property, but that still throws a throttle exception. I tried using the ContentIterator as defined here: http://msdn.microsoft.com/en-us/library/microsoft.office.server.utilities.contentiterator.aspx, but that still throws the query throttle exception. I tried specifying the RowLimit parameter in the ProcessListItems functions, as suggested by the first comment here: http://tomvangaever.be/blogv2/2011/05/contentiterator-very-large-lists/, but it still throws the query throttle exception. I tried using GetDataTable instead, still throws query throttle exception. I can't run this as admin, I can't raise the threshold limit, I can't raise the threshold limit temporarily, I can't override the lists throttling(i.e. list.EnableThrottling = false;), and I can't override the SPQuery(query.QueryThrottleMode = SPQueryThrottleOption.Override;). Does anyone know how to get items back in this situation or has anyone succesfully beaten the query throttle exception? Thanks.

My Query:

<OrderBy>
    <FieldRef Name='ID' Ascending='TRUE' />
</OrderBy>
<Where>
    <Geq><FieldRef Name='ID' /><Value Type='Counter'>0</Value></Geq>
</Where>

My ViewXml:

<View>
    <Query>
        <OrderBy><FieldRef Name='ID' Ascending='TRUE' /></OrderBy>
        <Where>
            <Geq><FieldRef Name='ID' /><Value Type='Counter'>0</Value></Geq>
        </Where>
    </Query>
    <RowLimit>2000</RowLimit>
</View>

Thanks again.

Answer 1

Not sure if you were able to get an answer to the issue above, hopefully this helps.

You need to add an indexed field so the query will know how many items you are returning, this can be done by adding the ItemEnumberationOrderByNVPField to your query.

If you look at the following MSDN Article, The second examples returns a string for the query titled: private static string CreateQuery()

On the second to last line the following has been added: queryBuilder.Append(ContentIterator.ItemEnumerationOrderByNVPField);"

to the query below which I believe is what you need to add to your query:

private static string CreateQuery()
{
    // This example 
    StringBuilder queryBuilder = new StringBuilder();
    queryBuilder.Append("<Where><Eq>");
    queryBuilder.AppendFormat(CultureInfo.InvariantCulture,"<FieldRef Name=\"{0}\" />", columnName);
    queryBuilder.Append("<Value Type=\"Boolean\">1</Value>");
    queryBuilder.Append("</Eq></Where>");
    queryBuilder.Append(ContentIterator.ItemEnumerationOrderByNVPField);

    return queryBuilder.ToString();
}