Andreas Grabner About the Author

Andreas Grabner has been helping companies improve their application performance for 15+ years. He is a regular contributor within Web Performance and DevOps communities and a prolific speaker at user groups and conferences around the world. Reach him at @grabnerandi

SharePoint: Page through SharePoint lists

SharePoint lists can contain thousands of items. We have all heard about the 2000 items limit that should not be exceeded in order to achieve good list performance. There are definitely performance impacts when exceeding this limit and there are ways to overcome this limit by using indexed columns and views.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 the number of items for the page

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 last data 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.

Comments

  1. great site

  2. There is a huge difference in using an SPQuery object and directly accessing the SPList. With SPQuery you have the chance to narrow down the items that you want to have and also the columns that you want to have – causing less stress on the database.

Comments

*


4 + one =