Writing Efficient Code in SharePoint Server

Article
07/24/2014

Applies to: SharePoint Server 2010

If you write custom solutions by using the SharePoint object model, you should be aware of common issues related to performance, extensibility, and scalability. This topic can help you effectively troubleshoot and improve the performance of existing SharePoint applications or write new SharePoint applications. In both cases, you need to understand how to make the SharePoint object model work efficiently, and how to apply general programming techniques to the SharePoint platform specifically.

Using SPQuery Objects

Well-designed queries can help you avoid performance problems that can occur over time as the size of your site's lists and folders increases. The following techniques can help you use SPQuery objects in the most efficient manner possible.

Use a bounded SPQuery object.

An SPQuery without a value for RowLimit will perform poorly and will fail on large lists. Specify a RowLimit between 1 and 2000 and page through the list if necessary. For a code sample that demonstrates how to do this, see Handling Large Folders and Lists.
Limit the result set.

If your query returns more items than the configured query threshold in Microsoft SharePoint Foundation 2010, the query will be blocked and you will not get results. The default query threshold is 5,000 items, but you can set it to a lower value. For Microsoft SharePoint Server 2010 queries, use ContentIterator.MaxItemsPerQuery and page through the results if you need all items.
Use indexed fields.

If you are querying on a field that is not indexed, and the resulting scan encounters more items in the list than the query threshold, the query will be blocked. Set SPQuery.RowLimit to a value that is less than the query threshold. The value of ContentIterator.MaxItemsPerQuery is ensured to be less than or equal to the threshold, which is the recommended value for SharePoint Server 2010.

Ensure you include one of the three OrderBy clauses—ContentIterator.ItemEnumerationOrderByID, ContentIterator.ItemEnumerationOrderByPath, or ContentIterator.ItemEnumerationOrderByNVPField—that enables the index to be used.

Without an OrderBy clause, your query could be blocked. SharePoint Server 2010 adds a default OrderBy clause that orders by content type, which ensures that folders are returned before list items. Unless you override this behavior with one of the three OrderBy clauses listed above, your query cannot take full advantage of using indexed fields, and it will be blocked whenever the query is not designed to be restrictive enough to return fewer than the maximum number of items. The following code example shows how to use the ContentIterator.ItemEnumerationOrderByNVPField clause. The example assumes that you are querying an indexed field.

SPQuery query = new SPQuery();
query.Query = 
"<Where><Eq><FieldRef Name=\"MyIndexedField\"/><Value Type=\"Text\">FieldValue</Value></Eq></Where>" 
+ ContentIterator.ItemEnumerationOrderByNVPField;
ContentIterator ci = new ContentIterator();
ci.ProcessItemsInList(query,
    delegate(SPListItem item)
    {
        // Work on each item.
    },
    delegate(SPListItem item, Exception e)
    {
        // Handle an exception that was thrown while iterating.
        // Return true so that ContentIterator rethrows the exception.
        return true;
    }
);

Dim query As New SPQuery()
query.Query = "<Where><Eq><FieldRef Name=""MyIndexedField""/><Value Type=""Text"">FieldValue</Value></Eq></Where>" & ContentIterator.ItemEnumerationOrderByNVPField
Dim ci As New ContentIterator()
ci.ProcessItemsInList(query,
                              Function(item As SPListItem)
                                  'Work on each item.
                              End Function,
                              Function(item As SPListItem, e As Exception)
                                  'Handle an exception that was thrown while iterating.
                                  'Return true so that ContentIterator rethrows the exception.
                                  Return True
                              End Function)

Using PortalSiteMapProvider

Use PortalSiteMapProvider (Microsoft Office SharePoint Server 2007 and Microsoft SharePoint Server 2010 only).

Steve Peschka's white paper Working with Large Lists in Office SharePoint Server 2007 describes an efficient approach to retrieving list data by using the PortalSiteMapProvider class. PortalSiteMapProvider provides an automatic caching infrastructure for retrieving list data. The GetCachedListItemsByQuery method of PortalSiteMapProvider takes an SPQuery object as a parameter, and then checks its cache to determine whether the items already exist. If they do, the method returns the cached results. If not, it queries the list and stores the results in a cache. This approach works especially well when you are retrieving list data that does not change significantly over time. When data sets change frequently, the class incurs the performance cost of continually writing to the cache in addition to the costs of reading from the database. Consider that the PortalSiteMapProvider class uses the site collection object cache to store data. This cache has a default size of 100 MB. You can increase the size of this cache for each site collection on the object cache settings page for the site collection. This memory, however, is taken from the shared memory available to the application pool and can therefore affect the performance of other applications. Another significant limitation is that you cannot use the PortalSiteMapProvider class in applications based on Windows Forms. The following code example shows how to use this method.

Good Coding Practice

Using PortalSiteMapProvider

// Get the current SPWeb object. 
SPWeb curWeb = SPControl.GetContextWeb(HttpContext.Current); 

// Create the query. 
SPQuery curQry = new SPQuery(); 
curQry.Query = "<Where><Eq><FieldRef Name='Expense_x0020_Category'/>
<Value Type='Text'>Hotel</Value></Eq></Where>"; 

// Create an instance of PortalSiteMapProvider. 
PortalSiteMapProvider ps = PortalSiteMapProvider.WebSiteMapProvider; 
PortalWebSiteMapNode pNode = ps.FindSiteMapNode(curWeb.ServerRelativeUrl) as PortalWebSiteMapNode; 

// Retrieve the items. 

SiteMapNodeCollection pItems = ps.GetCachedListItemsByQuery(pNode, "myListName_NotID", curQry, curWeb); 

// Enumerate through all of the matches. 
foreach (PortalListItemSiteMapNode pItem in pItems)
   { 
   // Do something with each match.
   }

' Get the current SPWeb object. 
Dim curWeb As SPWeb = SPControl.GetContextWeb(HttpContext.Current)

' Create the query. 
Dim curQry As New SPQuery()
curQry.Query = "<Where><Eq><FieldRef Name='Expense_x0020_Category'/> <Value Type='Text'>Hotel</Value></Eq></Where>"

' Create an instance of PortalSiteMapProvider. 
Dim ps As PortalSiteMapProvider = PortalSiteMapProvider.WebSiteMapProvider
Dim pNode As PortalWebSiteMapNode = TryCast(ps.FindSiteMapNode(curWeb.ServerRelativeUrl), PortalWebSiteMapNode)

' Retrieve the items. 

Dim pItems As SiteMapNodeCollection = ps.GetCachedListItemsByQuery(pNode, "myListName_NotID", curQry, curWeb)

' Enumerate through all of the matches. 
For Each pItem As PortalListItemSiteMapNode In pItems
   ' Do something with each match.
Next pItem

Share via

Writing Efficient Code in SharePoint Server

Using SPQuery Objects

Using PortalSiteMapProvider

Additional resources