Handling Large Folders and Lists

Article
10/20/2016

Applies to: SharePoint Foundation 2010

When the size of folders and lists increases, you must design custom code that works with them to optimize performance. Otherwise, your applications will run slowly and can cause service or page load timeouts. The two primary areas for concern when handling large folders and lists are the following:

Query throttling, which can cause the behavior of your code to change in unexpected and unpredictable ways over time as your site evolves and your queries begin to return items that exceed the query threshold.
Efficient retrieval of items from large folders and lists.

To address both concerns, you must understand how the object model interacts with folders and lists.

Throttling for Large List Queries

Microsoft SharePoint Foundation 2010 and Microsoft SharePoint Server 2010 apply a default query threshold of 5,000 items. Any custom code that relies on query result sets that can exceed this maximum will not perform as expected. Queries on lists consisting of more than 5,000 items that include fields that are not indexed in their query conditions will also fail, because those queries must scan all rows in a list. Follow the steps listed below to view and increase this limit or to enable the object model to override the limit:

To view and increase this threshold or to enable the object model to override the threshold

On the Central Administration site, under Application Management, click Manage Web Applications.
Click General Settings, and then click Resource Throttling.
View and update the threshold or allow the object model to override the limit.

Working with Folders and Lists

The following recommendations for addressing performance concerns when working with large folders and lists are based on the test results reported in Steve Peschka's white paper, Working with Large Lists in Office SharePoint Server 2007. These recommendations are also applicable to Microsoft SharePoint Server 2010. For additional guidance on using SPQuery and the PortalSiteMapProvider class, which applies specifically to SharePoint Server 2010, see Writing Efficient Code in SharePoint Server.

When you are working with folders and lists:

Do not use SPList.Items.

SPList.Items selects all items from all subfolders, including all fields in the list. Use the following alternatives for each use case.

Adding an item

Instead of calling SPList.Items.Add, use SPList.AddItem.

Retrieving all items in a list

Instead of using SPList.Items, use SPList.GetItems(SPQuery query) . Apply filters, if appropriate, and specify only the fields you need to make the query more efficient. If the list contains more than 2,000 items, paginate the list in increments of no more than 2,000 items. The following code example shows how to paginate a large list.

Good Coding Practice

Retrieving items with SPList.GetItems

SPQuery query = new SPQuery();
SPListItemCollection spListItems ; 
string lastItemIdOnPage = null; // Page position.
int itemCount = 2000
 
while (itemCount == 2000)
{
    // Include only the fields you will use.
    query.ViewFields = "<FieldRef Name=\"ID\"/><FieldRef Name=\"ContentTypeId\"/>";   
    query.RowLimit = 2000; // Only select the top 2000.
    // Include items in a subfolder (if necessary).
    query.ViewAttributes = "Scope=\"Recursive\"";
    StringBuilder sb = new StringBuilder();
    // To make the query order by ID and stop scanning the table, specify the OrderBy override attribute.
    sb.Append("<OrderBy Override=\"TRUE\"><FieldRef Name=\"ID\"/></OrderBy>");
    //.. Append more text as necessary ..
    query.Query = sb.ToString();
    // Get 2,000 more items.
 
    SPListItemCollectionPosition pos = new SPListItemCollectionPosition(lastItemIdOnPage);
    query.ListItemCollectionPosition = pos; //Page info.
    spListItems = spList.GetItems(query);
    lastItemIdOnPage = spListItems.ListItemCollectionPosition.PagingInfo;
    // Code to enumerate the spListItems.
    // If itemCount <2000, finish the enumeration.
    itemCount = spListItems.Count;

}

Dim query As New SPQuery()
Dim spListItems As SPListItemCollection
Dim lastItemIdOnPage As String = Nothing ' Page position.
Dim itemCount As Integer = 2000

Do While itemCount = 2000
    ' Include only the fields you will use.
    query.ViewFields = "<FieldRef Name=""ID""/><FieldRef Name=""ContentTypeId""/>"
    query.RowLimit = 2000 ' Only select the top 2000.
    ' Include items in a subfolder (if necessary).
    query.ViewAttributes = "Scope=""Recursive"""
    Dim sb As New StringBuilder()
    ' To make the query order by ID and stop scanning the table, specify the OrderBy override attribute.
    sb.Append("<OrderBy Override=""TRUE""><FieldRef Name=""ID""/></OrderBy>")
    '.. Append more text as necessary ..
    query.Query = sb.ToString()
    ' Get 2,000 more items.

    Dim pos As New SPListItemCollectionPosition(lastItemIdOnPage)
    query.ListItemCollectionPosition = pos 'Page info.
    spListItems = spList.GetItems(query)
    lastItemIdOnPage = spListItems.ListItemCollectionPosition.PagingInfo
    ' Code to enumerate the spListItems.
    ' If itemCount <2000, finish the enumeration.
    itemCount = spListItems.Count
Loop

The following example shows how to enumerate and paginate a large list.

SPWeb oWebsite = SPContext.Current.Web;
SPList oList = oWebsite.Lists["Announcements"];

SPQuery oQuery = new SPQuery();
oQuery.RowLimit = 10;
int intIndex = 1;

do
{
    Response.Write("<BR>Page: " + intIndex + "<BR>");
    SPListItemCollection collListItems = oList.GetItems(oQuery);

    foreach (SPListItem oListItem in collListItems)
    {
        Response.Write(SPEncode.HtmlEncode(oListItem["Title"].ToString()) +"<BR>");
    }

    oQuery.ListItemCollectionPosition = collListItems.ListItemCollectionPosition;
    intIndex++;
} while (oQuery.ListItemCollectionPosition != null);

 Dim oWebsite As SPWeb = SPContext.Current.Web
Dim oList As SPList = oWebsite.Lists("Announcements")

Dim oQuery As New SPQuery()
oQuery.RowLimit = 10
Dim intIndex As Integer = 1

Do
    Response.Write("<BR>Page: " & intIndex & "<BR>")
    Dim collListItems As SPListItemCollection = oList.GetItems(oQuery)

    For Each oListItem As SPListItem In collListItems
        Response.Write(SPEncode.HtmlEncode(oListItem("Title").ToString()) & "<BR>")
    Next oListItem

    oQuery.ListItemCollectionPosition = collListItems.ListItemCollectionPosition
    intIndex += 1
Loop While oQuery.ListItemCollectionPosition IsNot Nothing

Getting items by identifier

Instead of using SPList.Items.GetItemById, use SPList.GetItemById(int id, string field1, params string[] fields). Specify the item identifier and the field that you want.

Do not enumerate entire SPList.Items collections or SPFolder.Files collections.

The left column in Table 1 lists the methods and properties that if used will enumerate the entire SPList.Items collection, and cause poor performance and throttling for large lists. Instead, use the better-performing alternatives listed in the right column.

Table 1. Alternatives to enumerate SPList.Items

Poor Performing Methods and Properties	Better Performing Alternatives
SPList.Items.Count	SPList.ItemCount
SPList.Items.XmlDataSchema	Create an SPQuery object to retrieve only the items you want.
SPList.Items.NumberOfFields	Create an SPQuery object (specifying the ViewFields) to retrieve only the items you want.
SPList.Items[System.Guid]	SPList.GetItemByUniqueId(System.Guid)
SPList.Items[System.Int32]	SPList.GetItemById(System.Int32)
SPList.Items.GetItemById(System.Int32)	SPList.GetItemById(System.Int32)
SPList.Items.ReorderItems(System.Boolean[],System.Int32[],System.Int32)	Perform a paged query by using SPQuery and reorder the items within each page.
SPList.Items.ListItemCollectionPosition	ContentIterator.ProcessListItems(SPList, ContentIterator.ItemProcessor, ContentIterator.ItemProcessorErrorCallout) (Microsoft SharePoint Server 2010 only)
SPList.Items.ListItemCollectionPosition	ContentIterator.ProcessListItems(SPList, ContentIterator.ItemProcessor, ContentIterator.ItemProcessorErrorCallout) (SharePoint Server 2010 only)

Note

Using the SPList.ItemCount property is the recommended way to retrieve the number of items in a list. As a side effect of tuning this property for performance, however, the property can occasionally return unexpected results. For example, if you require the exact number of items, you should use the poorer performing GetItems(SPQuery query), as shown in the preceding code example.

Whenever possible, acquire a reference to a list by using the list's GUID or URL as a key.

You can retrieve an SPList object from the SPWeb.Lists property by using the list's GUID or display name as an indexer. Using SPWeb.Lists[GUID] and SPWeb.GetList(strURL) is always preferable to using SPWeb.Lists[strDisplayName]. Using the GUID is preferable because it is unique, permanent, and requires only a single database lookup. The display name indexer retrieves the names of all the lists in the site and then does a string comparison with them. If you have a list URL instead of a GUID, you can use the GetList method in SPWeb to look up the list's GUID in the content database before retrieving the list.

Do not enumerate entire SPFolder.Files collections.

The left column in Table 2 lists the methods and properties that inflate the SPFolder.Files collection and cause poor performance and throttling for large lists. Instead, use the better-performing alternatives in the right column.

Table 2. Alternatives to SPFolders.Files

Poor Performing Methods and Properties	Better Performing Alternatives
SPFolder.Files.Count	SPFolder.ItemCount
SPFolder.Files.GetEnumerator()	ContentIterator.ProcessFilesInFolder(SPFolder, System.Boolean, ContentIterator.FileProcessor, ContentIterator.FileProcessorErrorCallout) (SharePoint Server 2010 only)
SPFolder.Files[System.String]	ContentIterator.GetFileInFolder(SPFolder, System.String)Alternatively, SPFolder.ParentWeb.GetFile(SPUrlUtility.CombineUrl(SPFolder.Url, System.String) (SharePoint Server 2010 only)
SPFolder.Files[System.Int32]	Do not use. Switch to ContentIterator.ProcessFilesInFolder and count items during iteration. (SharePoint Server 2010 only)

Deleting Multiple Versions of a List Item

When you delete multiple versions of a list item, use the DeleteByID() method; do not use the Delete() method. You will experience performance problems if you delete each SPListItemVersion object from an SPListItemVersionCollection object. The recommended practice is to create an array that contains the ID properties of each version and then delete each version by using the SPFileVersionCollection.DeleteByID method. The following code examples demonstrate both the approach that is not recommended and the recommended approach to deleting all versions of the first item of a custom list.

Bad Coding Practice

Deleting each SPListItemVersion object

SPSite site = new SPSite("site url");
SPWeb web = site.OpenWeb();
SPList list = web.Lists["custom list name"];
SPListItem item = list.GetItemById(1); 
SPListItemVersionCollection vCollection = item.Versions;
ArrayList idList = new ArrayList();
foreach(SPListItemVersion ver in vCollection)
{
  idList.Add(ver.VersionId);
}
foreach(int verID in idList)
{
  SPListItemVersion version = vCollection.GetVersionFromID(verID); 
try
{
  version.Delete();
}
catch (Exception ex)
{
  MessageBox.Show(ex.Message);  
}
}

Good Coding Practice

Deleting each version of a list item by using the SPFileVersionCollection.DeleteByID method

SPSite site = new SPSite("site url");
SPWeb web = site.OpenWeb();
SPList list = web.Lists["custom list name"];
SPListItem item = list.GetItemById(1);
SPFile file = web.GetFile(item.Url);
SPFileVersionCollection collection = file.Versions;
ArrayList idList = new ArrayList();
foreach (SPFileVersion ver in collection)
{
  idList.Add(ver.ID);
}
foreach (int verID in idList)
{
try
{
  collection.DeleteByID(verID);
}
catch (Exception ex)
{
  MessageBox.Show(ex.Message);  
}
}

If you are deleting versions of items in a document library, you can use a similar approach by retrieving the SPListItem.File.Versions property, as in the following code example.

Good Coding Practice

Deleting each version of a list item in a document library by using the SPFileVersionCollection.DeleteByID method

SPSite site = new SPSite("site url");
SPWeb web = site.OpenWeb();
SPList list = web.Lists["custom list name"];

SPFile file = list.RootFolder.Files[0];
SPFileVersionCollection collection = file.Versions;
ArrayList idList = new ArrayList();
foreach (SPFileVersion ver in collection)
{
  idList.Add(ver.ID);
}
foreach (int verID in idList)
{
try
{
  collection.DeleteByID(verID);
}
catch (Exception ex)
{
  MessageBox.Show(ex.Message);  
}
}

Handling Large Folders and Lists

Throttling for Large List Queries

To view and increase this threshold or to enable the object model to override the threshold

Working with Folders and Lists

Deleting Multiple Versions of a List Item

See Also

Other Resources

Additional resources