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

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

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.

Related posts:

1. SharePoint: Only request data that you really need One of the main performance problems that we can witness...
2. Performance Considerations when using SharePoint Object Model The SharePoint Object Model allows external applications or hosted WebParts...
3. SharePoint: Page through SharePoint lists SharePoint lists can contain thousands of items. We have all heard...
4. SharePoint: Using Batch-Updates to speed up performance? In my previous posts about SharePoint I focused on how...
5. SharePoint: Monitoring individual List usage and performance We all know that SharePoint list performance can degrade the...