SharePoint Development - Improving the Performance of the For Each Loop and SPQuery Object

Recently I have been very focused on optimizing SharePoint code and so I thought I'd put together all my techniques for writing more effecient code when working with the For Each Loop and/or SPQuery Object.  If anyone has any additional techniques please post comments!

References:
http://www.zimmergren.net/archive/2008/05/04/how-to-sharepoint-queries.aspx
http://msdn.microsoft.com/en-us/library/ee558807(v=office.14).aspx
http://andreasgrabner.sys-con.com/node/1348618/mobile

1.For Large Lists Use SPQuery with For Each Loops to reduce the number of items returned:

SPList myList = SPContext.Current.Web.Lists["Example List"];
StringBuilder camlQuery = new StringBuilder();
camlQuery.Append("<Where>");
camlQuery.Append("<Eq>");
camlQuery.Append("<FieldRef Name='Category' />");
camlQuery.Append("<Value Type='Text'>");
camlQuery.Append("{Category 1}");
camlQuery.Append("</Value>");
camlQuery.Append("</Eq>");
camlQuery.Append("</Where>", ID); 

SPQuery query = new SPQuery();
query.Query = camlQuery.ToString();
//ID comes as a parameter to the method being called

SPListItemCollection listItems = myList.GetItems(query);
for(int i=0;i<100 && i< listItems.Count;i++) {
  SPListItem myListItem = listItems[i];
  htmlWriter.Write(myListItem["Title"]);}

2.Use SPListItemCollection instead of SPList:

In the example below, every time we access the Items property in the For Loop condition (myList.Items.Count) it queries all items from the Content Database - the retrieved items are never cached!
 
SPList myList = SPContext.Current.List;
for(int i=0;i<100 && i< myList.Items.Count;i++) {
  SPListItem myListItem = myList.Items[i];
  htmlWriter.Write(myListItem ["Title"]);}

In this next example the database is queried only once and from then on we work with an in-memory collection of all the retrieved list items.  In this simple example alone we saved an additional 199 trips to the database!

SPListItemCollection myItems = SPContext.Current.List.Items;
for(int i=0;i<100 && i< myItems.Count;i++) {
  SPListItem myListItem = myItems[i];
  htmlWriter.Write(myListItem["Title"]);}

3.Set the SPQuery RowLimit Property to limit the number of items returned:

SPQuery myQuery = new SPQuery();
myQuery.RowLimit = 100;
myQuery.ListItemCollectionPosition = prevItems.ListItemCollectionPosition;
//The code above will start the cursor at the previous position
SPListItemCollection items = SPContext.Current.List.GetItems(myQuery);

4.Limit the number of returned columns by using the SPQuery ViewFields property:

SPQuery myQuery = new SPQuery();
myQuery.ViewFields =
"<FieldRefName='ID'/><FieldRefName='Title'/>";

5.Limit the specific elements retrieved using CAML:

SPQuery query = new SPQuery();
query.Query = “<Where><Eq><FieldRefName=\"ID\"
/><ValueType=\"Number\">15</Value></Eq></Where>”;

6.Query against indexed fields:

   a. Index fields are set at the list or library level (for more information click here)
   b. Index fields are stored in the Content Database – not in the Search index (for more information click here)
   c. SPQuery will only employ the first index field in your query statement
   d. Index fields add some overhead to a SharePoint list and therefore should be used judiciously

7. Use RowLimit and ListItemCollectionPosition properties to reduce the number of items returned per query by creating a paging effect:

   a. The SPQuery object provides the property ListItemCollectionPosition that allows you to specify the start position of your query page. This property can be used for any further page iteration to define the starting point of the next page.
   b. The RowLimit property allows you to specify how many items to retrieve per page.
   c. Here is a code example that combines these methods:

SPQuery query = new SPQuery();
query.RowLimit = 10; //Page size is set by RowLimit
do{  
SPListItemCollection items = SPContext.Current.List.GetItems(query);  
//do something with the page result    
query.ListItemCollectionPosition = items.ListItemCollectionPosition;}
//the code above sets the position cursor for the next iteration  
while (query.ListItemCollectionPosition != null)

8.List View Threshold and SPQuery (SharePoint 2010 Only)

   a. SPS 2010 introduces a new capability called Throttling which allows a SharePoint Farm Administrator to set a List View Threshold to limit the number of results that can be returned in a user query
   b. This same capability will allow SharePoint Farm Administrator  to enable and/or disable the developers ability to programmatically override the List View Threshold (for more information click here)
   c. Without an OrderBy clause, a SPQuery request can be blocked whenever the query is not designed to be restrictive enough to meet the List View Threshold.
   d. SharePoint Server 2010 adds a default OrderBy clause that orders by content type, which ensures that folders are returned before list items.
   e. Developers should override this behavior with one of custom OrderBy clauses so that their queries can take full advantage of using indexed fields.
   f. There are three OrderBy clauses:
      i. ContentIterator.ItemEnumerationOrderByID
      ii. ContentIterator.ItemEnumerationOrderByPath
      iii. ContentIterator.ItemEnumerationOrderByNVPField
   g. Why use the ContentIterator.ItemEnumerationOrderByNVPField Property: 
      i. Using this property overrides any OrderBy clause you may have in your SPQuery and assures that an indexed field is used for sorting
      ii. Using this property keeps your query from being blocked if your where clause is not using indexed fields and would bring back more than the throttling limit (assuming the Farm Administrator has enabled programmatic override of the List View Threshold)
      iii. Using this property overcomes the need to store results in memory and/or a temp table for sorting
      iv. Here is a code example:

StringBuilder camlQuery = new StringBuilder();
camlQuery.Append("<Where>");
camlQuery.Append("<Eq>");
camlQuery.Append("<FieldRef Name='myIndexedField' />");
camlQuery.Append("<Value Type='Text'>");
camlQuery.Append("{FieldValue}");
camlQuery.Append("</Value>");
camlQuery.Append("</Eq>");
camlQuery.Append("</Where>"); 
camlQuery.Append(ContentIterator.ItemEnumerationOrderByNVPField);

SPQuery query = new SPQuery();
query.Query = camlQuery.ToString();
ContentIterator oContentIterator = new ContentIterator();
oContentIterator.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;
    }

Writing efficient code is always best practise and since the For Each Loop and SPQuery Object are used so frequently it really pays to learn how to optimize these elements in your custom solution.

I hope that helps!

Tom