Updated: July 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
1.On the Central Administration site, under Application Management, click Manage Web Applications.
2.Click General Settings, and then click Resource Throttling.
3.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
VBC#C++F#JScriptCopySPQuery 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 = "
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("
//.. 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; } The following example shows how to enumerate and paginate a large list. VBC#C++F#JScriptCopySPWeb oWebsite = SPContext.Current.Web; SPList oList = oWebsite.Lists["Announcements"]; SPQuery oQuery = new SPQuery(); oQuery.RowLimit = 10; int intIndex = 1; do { Response.Write("
Page: " + intIndex + "
");
SPListItemCollection collListItems = oList.GetItems(oQuery);
foreach (SPListItem oListItem in collListItems)
{
Response.Write(SPEncode.HtmlEncode(oListItem["Title"].ToString()) +"
");
}
oQuery.ListItemCollectionPosition = collListItems.ListItemCollectionPosition;
intIndex++;
} while (oQuery.ListItemCollectionPosition != null);
◦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.ItemsPoor 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.FilesPoor 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
VBC#C++F#JScriptCopySPSite 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
VBC#C++F#JScriptCopySPSite 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
VBC#C++F#JScriptCopySPSite 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);
}
}
