SharePoint - Iterate through large list

Retrieving list items from SharePoint list from code is the most basic thing, and every SharePoint developer is familiar with it.

This is the easiest way for retrieving items of a SharePoint list from code:

using(SPSite siteColl = new SPSite("SiteName"))
{

    using(SPWeb web = siteColl.OpenWeb())

    { 
        SPList list = web.RootWeb.Lists["ListName"];
        SPListItemCollection items = list.Items;
 
        foreach (SPListItem listItem in items)
        {
            Response.Write("Item title: " + listItem["Title"].ToString());
        } 

    }

}

But, this is a very poor solution, because this row 

SPListItemCollection items = list.Items; 

returns all items of a list at once. If your list has large amount of items (for example, more then 2000), then this can be a serious performance issue.If you have extremely large list, like 1 000 000 items, this code will certainly crash your server.


The solution is to using SPQuery class and its RowLimit property like this:

using(SPSite siteColl = new SPSite("SiteName"))
{

    using(SPWeb web = siteColl.OpenWeb())

    { 
        SPList list = web.RootWeb.Lists["ListName"];

        SPQuery query = new SPQuery();
        query.Query = "<Query><OrderBy><FieldRef Name='ID' /></OrderBy></Query>";

        //Scope="Recursive" retrieves items from all folders and subfolders in a list
        query.ViewAttributes = "Scope=\"Recursive\"";
        query.RowLimit = 100;

        do
        {
            SPListItemCollection items = list.GetItems(query);

            foreach (SPListItem listItem in items)
            {
                Response.Write("Item title: " + listItem["Title"].ToString());
            }

            query.ListItemCollectionPosition = items.ListItemCollectionPosition;

        } while (query.ListItemCollectionPosition != null);
        

    }

}

With SPQuery, execution is faster. This query returns all fields from a list, but you can make it even faster if you define only the fields that you need, and not all of them. RowLimit property of SPQuery class ensures that only specific number of items will be processed in one iteration.