Update Nov 27, 2014: Just posted this YouTube video that shows how to easily identify top SharePoint Performance Problems: SharePoint Performance Analysis in 15 Minutes

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