Iteration through SPList Items

As a SharePoint User, everyone should know that SharePoint is a fast-growing platform. It grew from a small list and document management application into an application development platform on top of ASP.NET using its own API to manage content in the SharePoint Content Database.

Over the years many things have changed, but some have not. I remember one quote from “Wisdom of The Orange Woodpecker” : “Change is not pleasant, But change is constant. Only when we change and grow, We’ll see a world we never know.”

SharePoint still uses a single database table to store ALL items in any SharePoint List. This brings me straight into few problems I have seen when working with companies that implemented their own solution based on SharePoint.

I want to share my findings while working on different SharePoint projects.

Iteration: Iterating through SPList Items

As a developer perspective, SPList object can be accessed either using it from current SPContext or creating a SPList object to access a list identified by its name.

SPList provides an Item’s property that returns a SPListItemCollection object.

The following code snippet shows one way to display the “Title” column of the first 200 items in the current SPList object:

At a first glance it looks good. Although the code works fine and performs great in a local environment for fewer records.

The problem is the way the Items property is accessed. The Item’s property queries all items from the Content Database for the current SPList and “tactlessly” does that every time we access the Item’s property. The retrieved items “are not cached”.

In above sample code, we access the Item’s property twice for every loop iteration:

1. To retrieve the Count
2. To access the actual Item identified by its index

While analyzing the actual ADO.NET Database activity of that loop shows us interesting results

Execute Count 1000

Execution Average 26 [ms]

Execution Total 5370 [ms]

Note: 1000 SQL Statements get executed when iterating through SPList.Items

Problem: The same SQL Statement is executed all over again which retrieves ALL items from the content database for this list. In my example above I had 1000 SQL calls totaling up to more than 5 second in SQL Execution Time.

Solution: The solution for the problem is easy but unfortunately still rarely used. We simply need to store the SPListItemCollection object returned by the Items property in a variable and use it in loop:

By using above code we are working on an in-memory collection of all retrieved items as this queries the database only once.

Please read for other performance mistakes Memory Leaks with SharePoint Objects