Iterating Big SharePoint Lists In Small Chunks

Why would anyone want to iterate SharePoint lists in small chunks? because of two main reasons, the first one is because if the results of one of your queries is too big (say 2000 items) it gets really slow to work with it and second because you might just need the top 20 items that fulfil a condition and that condition is too complex to be represented in the first CAML query.

I am using query pagination to do it and it’s about 75% faster than processing all of the items in the first query (this depends on the amount of data you have in the list)

It basically goes as follows, you set the RowLimit to a sensible number (you also want to keep the number of round trips to a minimum) then you query the SharePoint List until the number of items that fulfil the condition hits the target or you run out of items. (I still haven’t tested if it’s faster to go through every item in a big list using this method or retrieving all at once but something tells me this will be faster)

/// <summary>
/// Returns the top MaxResults items in the query
/// </summary>
internal static List<SPListItem> GetTopItems(SPWeb web, uint MaxResults = 0)
{
    SPList ListToQuery = web.Lists.GetListByInternalName("ListInternalName");


    List<SPListItem> result = new List<SPListItem>();

    SPQuery query = new SPQuery();
    query.Query = string.Format(@"<Where><Eq><FieldRef Name='State' /><Value Type='Choice'>Live</Value></Eq></Where>");
    query.ViewAttributes = "Scope='RecursiveAll'";
    query.RowLimit = MaxResults + 1;

    SPListItemCollection QueryResults;
    string PagingInfo = string.Empty;

    while (result.Count < MaxResults && PagingInfo != null)
    {
        //We set the beginning of the query to the last item in the previous page
        query.ListItemCollectionPosition = new SPListItemCollectionPosition(PagingInfo);

        //We get the page of items
        QueryResults = ListToQuery.GetItems(query);

        foreach (SPListItem item in QueryResults)
        {
            //We process the items and add them to the result list
            if (ComplexProcessing(item))
                result.Add(item);
        }

        //We set PagingInfo to the last item retrieved in the query
        if (QueryResults.ListItemCollectionPosition != null)
            PagingInfo = QueryResults.ListItemCollectionPosition.PagingInfo.ToStringSafe();
        else //PagingInfo will be null if we reach the end of the pagination
            PagingInfo = null;
    }

    return result;
}

This approach to paginated CAML queries have improved the performance of my site hugely, I hope it serves you as well.