In Part 1 we looked at the problem of the query throttle in SharePoint 2010, and in Part 2 we looked at some of the ways of getting around that. In this part, though, we’ll look at what I think is the best way – the ContentIterator control.

The ContentIterator is a way of getting items either in batches or as individual items as a way of mitigating the effects of large lists and query throttling. As it gets smaller numbers of items, it can avoid the query throttle limit. Unfortunately, it can also suffer from it too, and it isn’t particularly obvious how to make it actually work on larger lists.

Let’s look at examples of code for using the content iterator. Note that our ‘Colour’ column in these examples is Indexed:

Retrieving items one-at-a-time:

ContentIterator ci = new ContentIterator("Single Item Example);
SPQuery qry = new SPQuery();
qry.Query = "<Where><Eq><FieldRef Name='Colour'/><Value Type='Text'>Pink</Value></Eq></Where>";
qry.Query = qry.Query + ContentIterator.ItemEnumerationOrderByNVPField;
ci.ProcessListItems(list, qry,
    delegate(SPListItem item)
    {
         // Do stuff with the item
    },
    delegate(SPListItem item, Exception ex)
    {
      // Handle an exception. Return TRUE to rethrow the exception, FALSE to keep iterating  return false;
    }
 );

Retrieving items in batches:

ContentIterator ci = new ContentIterator("Batch Example);
SPQuery qry = new SPQuery();
qry.Query = "<Where><Eq><FieldRef Name='Colour'/><Value Type='Text'>Pink</Value></Eq></Where>";
qry.Query = qry.Query + ContentIterator.ItemEnumerationOrderByNVPField;
ci.ProcessListItems(list, qry,
    delegate(SPListItemCollection items)
    {
        foreach (SPListItem itm in items)
        {
            //Process each item in batch!
        }
    },
    delegate(SPListItemCollection item, Exception ex)
    {
         // Handle an exception. Return TRUE to rethrow the exception, FALSE to keep iterating  return false;
    }
);

Note the differences in the delegates – one takes an SPListItem, the other an SPListItemCollection.

Now, when I first tried using the content iterator control I did not use the ContentIterator.ItemEnumerationOrderByNVPField constant. Without the OrderBy clause this added, I would receive an SPQueryThrottledException. I only thought to add it as I found the example of paging an SPQuery in the article “Handling Large Lists and Folders” and noticed that it was identical to the constant in the ContentIterator control.

There are other ‘Order By’ constants in the ContentIterator control, but they still resulted in an SPQueryThrottledException (at least in my test system). I’m also not clear what is special about the order:

<OrderBy UseIndexForOrderBy='TRUE' Override='TRUE' />

Now, if you dig around some of the documentation that’s online you’ll find some slightly dodgy examples. For example, “Using the Content Iterator” shows using a Query of the form:

string query1 = @"<View>
<Query>
<Where> <And> <BeginsWith> <FieldRef Name='SKU' /> <Value Type='Text'>S</Value> </BeginsWith> </And> </Where>
</Query>
</View>";

This actually causes the ContentIterator to process every item in the list. That might be useful – but if you’ve got a where clause I’m guessing that you only want a subset. Use queries of the form:

<Where><Eq><FieldRef Name='Colour'/><Value Type='Text'>Pink</Value></Eq></Where><OrderBy UseIndexForOrderBy='TRUE' Override='TRUE' />

… without the <View> or <Query> nodes.

You can also get all items in the list by using empty queries:

qry.Query = ContentIterator.ItemEnumerationOrderByNVPField;
ci.ProcessListItems(...

Conclusion

The ContentIterator is a good way of working with large lists. You can get item-by-item, or in batches, which is nice. Sort order may be a problem if you need your items in a particular order.

Again, it’s something that would seem well suited to being used in a Timer Job. Quite simply, processing lots of items takes time! The ContentIterator doesn’t seem especially slow – but processing lots of items in large lists does take time.