The wrong way to iterate through SharePoint SPList Items

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

The reason for that is because in every loop we request a new SPListItemCollectionobject 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

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

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.