Besides these considerations its also important to be smart when accessing data in the list. As already explained in the previous post – only accessing the data that you need can take a lot of pressure of the SharePoint Content Database. Additionally to that the SharePoint Object Model provides additional features to enhance access to list items.

Scenario 4: Paging through SharePoint list items with SPQuery

Paging data is a technique that we all well know from rich client applications or from web applications using e.g.: data grids. Paging allows easy navigation for the end-user and – if implemented correctly – reduces the load on the underlying database.

The SPQuery object provides the property ListItemCollectionPosition that allows you to specify the start position of your query page. RowLimit allows you to specify how many items to retrieve per page. Lets have a look at some sample code:

SPQuery query = new SPQuery();
query.RowLimit = 10; // that is our page size
do
{
  SPListItemCollection items = SPContext.Current.List.GetItems(query);
  // do something with the page result
 
  // set the position cursor for the next iteration
  query.ListItemCollectionPosition = items.ListItemCollectionPosition;
} while (query.ListItemCollectionPosition != null)

SPList.GetItems takes the query and only returns 10 items each time GetItems is called. The SPListItemCollection offers the property ListItemCollectionPosition which acts like a cursor on the SharePoint list. This property can be used for any further page iteration to define the starting point of the next page. Using dynaTrace we can see what happens on the database:

Every GetItems call retrieves RowItem + 1 items for the page

Looking closer at one of the SQL Statements shows us that the combination of the SELECT TOP and WHERE clause are used to retrieve the items of a certain page:

WHERE clause filters based on unique item id of the last item of the previous data page

Conclusion

Data Paging is built into SharePoint’s SPQuery object. Its a useful feature that you can use to retrieve batches of data instead of retrieving all items of a list. Consider using this option in order to minimize the activity on the database. Its enough to just request the data that you need and its best to let that work done by the database.

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: Lookup value Performance In SharePoint you can define lookup columns in your lists....
4. SharePoint ListItem Performance SharePoint provides a powerful object model to retrieve and manipulate...
5. The wrong way to iterate through SharePoint SPList Items There are multiple ways to iterate through the items of...