Handling Large List with Content Iterator

1. Reading all items in Large List

In SharePoint 2010, when you execute SPQuery on Large List, you get exception "The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator". To avoid this exception and read list items by batch we can use Content Iterator.

There are lot of methods available with ContentIterator, here we discuss about http://msdn.microsoft.com/en-us/library/ee560760%28v=office.14%29.aspx

To use ContentIterator include Microsoft.Office.Server.dll available in 14/ISAPI/ and include namespace Microsoft.Office.Server.Utilities.

Advantage:

Fetches list items as a batch, load is reduced.
If indexed column condition return more value than List View Threshold, it handles by batch. Normal SPQuery fails in this condition.
We can stop batch processing any time.

Disadvantage:

You cannot include Non-Indexed column in SPQuery condition.

//Run as console application

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SharePoint;
using Microsoft.Office.Server.Utilities;

namespace ContentIteratorListItemCollBatch
{
    class Sample
    {
        static int NumberOfBatch = 0, NumberOfItemsRead = 0, NumberOfException = 0;

        static void Main(string[] args)
        {
            using (SPSite site = new SPSite("your site url"))
            {
                using (SPWeb web = site.OpenWeb())
                {
                    SPList list = web.GetList("Lists/LargeList/AllItems.aspx"); //your list url
                    ContentIterator ci = new ContentIterator("Reading All Items");

                    SPQuery qry = new SPQuery();
                    qry.QueryThrottleMode = SPQueryThrottleOption.Strict; //Ensuring that all users come under List View Threshold. (Including farm admins / box administrators).

                    qry.RowLimit = 2000; //Number of Items read in a batch. But it should be less than List View Threshold.

                    qry.Query = qry.Query + ContentIterator.ItemEnumerationOrderByID; //Not Required, Include for faster output.
                    //Don't use ContentIterator.ItemEnumerationOrderByNVPField, it gets into infinite loop.

                    ci.ProcessListItems(list, qry, ProcessItemColl, ProcessErrorColl);
                    Console.WriteLine("\nBatch count: " + NumberOfBatch + "\n\nTotal number of items read: " + NumberOfItemsRead);
                    Console.ReadLine();
                }
            }
        }

        static public bool ProcessErrorColl(SPListItemCollection itemColl, Exception e)
        {
            // process the error
            NumberOfException++;
            return true;
        }
        static public void ProcessItemColl(SPListItemCollection itemColl)
        {
            //Work on the ListItem Collection object with your own condition
            //foreach (SPListItem item in itemColl)
            //{

            //}
            Console.WriteLine("Number of Items Read: " + itemColl.Count);
            NumberOfBatch++;
            NumberOfItemsRead += itemColl.Count;
        }
    }
}

My large list contains 25,000 items. you can see from output, that it read 25,000 items by batch of 2000 items.

Output

Number of Items Read: 2000
Number of Items Read: 2000
Number of Items Read: 2000
Number of Items Read: 2000
Number of Items Read: 2000
Number of Items Read: 2000
Number of Items Read: 2000
Number of Items Read: 2000
Number of Items Read: 2000
Number of Items Read: 2000
Number of Items Read: 2000
Number of Items Read: 2000
Number of Items Read: 1000

Batch count: 13

Total number of items read: 25000

2. Reading items in a Large List with condition

Ensure following conditions have been met.

Only Indexed column is allowed in where condition.
You should include ContentIterator.ItemEnumerationOrderByNVPField.

In below code, Title is a indexed column. As soon as custom list is created, title is made as index column.

//Run as Console Application

qry.Query = @"<Where><Contains><FieldRef Name='Title' /><Value Type='Text'>9</Value></Contains></Where>";

                    qry.Query = qry.Query + ContentIterator.ItemEnumerationOrderByNVPField;
                    //Have to include this line.

                    ci.ProcessListItems(list, qry, ProcessItemColl, ProcessErrorColl);
                    Console.WriteLine("\nBatch count: " + NumberOfBatch + "\n\nTotal number of items read: " + NumberOfItemsRead);
                    Console.ReadLine();
                }
            }
        }

        static public bool ProcessErrorColl(SPListItemCollection itemColl, Exception e)
        {
            // process the error
            NumberOfException++;
            return true;
        }
        static public void ProcessItemColl(SPListItemCollection itemColl)
        {
            //Work on the ListItem Collection object with your own condition
            //foreach (SPListItem item in itemColl)
            //{

            //}
            Console.WriteLine("Number of Items Read: " + itemColl.Count);
            NumberOfBatch++;
            NumberOfItemsRead += itemColl.Count;
        }
    }
}

In SPQuery, If indexed field fetches more than List View Threshold limit, it will fail. The ContentIterator handles it by batch processing.

Output

Number of Items Read: 2000
Number of Items Read: 2000
Number of Items Read: 2000
Number of Items Read: 2000
Number of Items Read: 233

Batch count: 5

Total number of items read: 8233

Handling Large List with Content Iterator - SharePoint 2010

Thursday 11 April 2013