Andreas Grabner About the Author

Andreas Grabner has been helping companies improve their application performance for 15+ years. He is a regular contributor within Web Performance and DevOps communities and a prolific speaker at user groups and conferences around the world. Reach him at @grabnerandi

The wrong way to iterate through SharePoint SPList Items

There are multiple ways to iterate through the items of a SharePoint list by using the SharePoint Object Model. One approach – which I’ve seen before in a real life SharePoint Application – may work fine on the developers machine or on very tiny lists. But it is going to ruin your performance once executed on a list that exceeds a couple of hundred items.Scenario 2: How to iterate through items in a SharePoint list using SPList?

Lets start by looking at a code snippet that can be used in a WebPart to access the first 100 items from the SharePoint list of the current context:

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

Assuming that there are at least 100 items in the list. How many roundtrips to the database is this code going to make in order to retrieve the 100 Title’s of the first 100 SharePoint list items? You might be surprised. Its a total of 200 database calls as you can see from the database view when analyzing the transaction executing the above code:

200 SQL Statements get executed when iterating through SPList.Items

200 SQL Statements get executed when iterating through SPList.Items

The reason for that is because in every loop we request a new SPListItemCollection object when accessing the Items property. The Items property is not cached and therefore always requests all items from the database again. Here is how the first loop iterations look like in the PurePath:

Every access to the Items property executes the same SQL statement again

Every access to the Items property executes the same SQL statement again

The CORRECT way to do it

The correct way to do it is of course to store the Items property return value in a SPListItemCollection variable. With this the database is only queried once and we will then iterate over the result set that is stored within the collection object. Here is the changed sample code:

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

Resulting in the following PurePath.

Storing the Items property in a variable elminiates 99.5% of the database calls

Storing the Items property in a variable elminiates 99.5% of the database calls

Conclusion

Many properties in SharePoint return new object instances every time you access them. In order to build good software based on the Microsoft SharePoint Platform its necessary to understand what is going on under the hood. This will eliminate “surprises” once your custom code is first executed with real life data.

There are additional ways to optimize access to data stored in SharePoint lists. I will cover that in my next posts.

Update: Read the summary blog of my blog series about The Top SharePoint Performance Problems. As SharePoint is built on the .NET Platform you might also be interested in my latest White Papers about Continuous Application Performance for Enterprise .NET Systems

Comments

  1. Very nice article and defenitely a permanent link to your blog from my blog :)

    Keep up the good work!

  2. I wonder if you tested this behavior in Release build mode?
    I think that it could work normally, because VS will optimize your calls

  3. The above examples behave the same way in debug or release. Accessing the property is actually invoking the get accessor method. As the get accessor is in fact a method the compiler cannot just optimize the call for you as there can be logic in the accessor that needs to be exected each time the property is accessed.

    There is however a better way to iterate through the list – namely – using a foreach loop. Maybe thats what you are refering to.
    The foreach loop is compiled in a way that the propertie’s IEnumerator return value is stored in a local variable. This IEnumerator is then used to iteratore over each element.

  4. Gute Arbeit hier! Gute Inhalte.

  5. I use SPList.ItemCount instead and/or an alternative access method (eg. GetItems() with a CAML query).

    See http://msdn.microsoft.com/en-us/library/bb687949.aspx

  6. @Michhes: using SPList.ItemCount is a good way of getting the number of elements. It doesn’t require a roundtrip to the database as the itemcount is kept in memory. Check out my other blogs on the SharePoint topic – you will find some more useful information about the SharePoint API

  7. Andreas,

    First, you should very rarely use a for index to iterate any collection in SharePoint. The SharePoint API will “do the right thing” (including using cache when appropriate, using good SQL query techniques, and the pre-creation of the next object in anticipation of the likely continued loop) in most circumstances when you use foreach for iteration instead. So, the code above would better be written as follows:

    SPListItemCollection items =
    SPContext.Current.List.Items;
    foreach(SPListItem item in items)
    {
    htmlWriter.Write(item["Title"]);
    }

    Not only is this more performant but it is easier to read so general supportability will improve too. I’ll leave the suspicious SPContext.Current.List and htmlWriter for someone else to take issue with.

    Second, retrieving the entire SPListItemCollection into memory can have it’s own set of problems. Consider the situation where there are thousands of list items with dozens of columns. That will produce quite a memory footprint just to write out the title of each SPListItem.

    I would highly recommend that you consider a CAML query that restricts the fields and may even filter the list items retrieved (or use LINQ to SharePoint 2010 which will generate a CAML query) and the GetItems method.

    The following example code will run in a Console App and it reads the SPListItem with a Title of “One” from the Tasks list in a Site Collection found at http://localhost. Even though this includes a sort, it is far more performant than the iteration originally demonstrated in this blog post. I parsed it down to just the Title of a single SPListItem to give the most comprehensive yet still simple example:

    using (SPSite siteCollection =
    new SPSite(“http://localhost”))
    {
    SPWeb web = siteCollection.RootWeb;
    SPList list = web.Lists["Tasks"];

    if (null != list)
    {
    StringBuilder sb = new StringBuilder();

    sb.Append(“”)
    .Append(” “)
    .Append(” “)
    .Append(” One”)
    .Append(” “)
    .Append(“”)
    .Append(“”)
    .Append(” “)
    .Append(“”);

    SPQuery qry = new SPQuery();
    qry.ViewFields = “”;
    qry.Query = sb.ToString();
    qry.ViewFieldsOnly = True;

    SPListItemCollection items = list.GetItems(qry);
    foreach (SPListItem item in items)
    {
    Console.WriteLine(item["Title"]);
    }
    }

    Console.WriteLine(“—Done”);
    Console.ReadLine();
    }

    For even more robust sample code, see the ViewFields entry in the SDK:
    http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.spquery.viewfields.aspx

    HTH,

  8. Since my response didn’t show up well in the comments section here. I’ve reposted my response on my own blog:
    http://sharepoint.mindsharpblogs.com/Todd/archive/2010/07/02/Performant-Iteration-of-an-SPListItemCollection.aspx

  9. Hi Todd
    Thanks for your comment. I wrote a series of blog entries about SharePoint Performance on Lists and Views. If you click on the SharePoint tag you will find all my other blogs. I also cover CAML and how it allows you to be very specific with not only querying fewer elements but also returning fewer columns (Just query what you really need).
    I also cover your proposed solution with iterating through the list.
    The reason why I posted all these blog posts is because I’ve seen several people (clients of ours) using the SharePoint Object Model in a way that is very inefficient. I hoped to educate people with these blogs on how to do better.

    Thanks again – postings and comments like yours will help the SharePoint Devs out there to get a better understanding of the internals of SharePoint and how to best use it

  10. Andreas,

    Awesome. I didn’t know about the other articles in your series. Keep up the good work then. : )

  11. Shaheen Aggarwal says:

    Thanks, Nice article!

Trackbacks

  1. [...] how to iterate, check this blog. Possibly related posts: (automatically generated)Java generics gotchaMore ArcGIS Geoprocessing [...]

  2. [...] back at the previous two posts (Scenario 1, Scenario 2) you can see that the executed SQL Statement always selected ALL items from requested SharePoint [...]

  3. [...] readings: The wrong way to iterate through SharePoint SPList Items and Performance Considerations when using the SharePoint Object [...]

Comments

*


+ two = 8