Custom code to increase performance for retrieval of SharePoint large lists or libraries

SharePoint is no doubt one of the most trusted and a popular platform by Microsoft. From being a small list document management application, it has grown into an application development platform many developers prefer to use to develop applications. It has been around for almost a decade to be now in the top content database and management platforms. It sits on top of ASP.NET using its own API to manage content in the SharePoint.

In one of our projects, the client requirement was to present the data through dashboards from different libraries which contain a large number of data along with pagination. While fetching and displaying the data from the libraries we faced serious performance issues like loading which kills the page a few times. To overcome this, we studied best approaches to increase the performance. A few of those approaches are mentioned below.

The objective of this blog is to provide you with information about the performance of large document libraries and large lists. This article focuses on the performance characteristics of large lists and on how different configurations affect the performance of large lists of the farm. Creating and implementing large lists still requires careful planning. You must consider many factors, such as information architecture, performance, disaster recovery, and governance. This article covers information architecture and features that are used to implement large lists, and the effect that specific configurations have on performance.

There are also some key design choices that you must make that can affect large list performance. These include permissions, the number of columns added to a list, the number of lookup columns in views, and the folders and indexes that are used to organize a list. These decisions affect performance of lists, and this effect becomes much bigger as the size of a list increases. This article explains how you can appropriately design a large list that meets performance requirements while also meeting business requirements and providing a good user experience.

List View: List views always access the Microsoft SQL Server database. This results in slower query performance and larger load on SQL Server resources than other methods. List views also render mostly in HTML which results in slower page load times. We should use list views when we have rich column data and access to list item actions. In high read and query scenarios, we should consider using other query methods.

In order to fetch and display the data from the SharePoint list using the code logic we have different ways:

• Iterating through SPList Items

As developer gets access to an SPList object – a developer typically uses it either from current SPContext or creates an SPList object to access a list identified by its name. SPList provides an item’s property that returns an SPListItemCollection object. The following code snippet shows one way to display the title column of the first 100 items in the current SPList object:

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"]);
}

Here the problem is the way the items property is accessed. In the loop example, we access the Item’s property twice for every loop iteration – once to retrieve the count, and once to access the actual item identified by its index.

• SPQuery

b) Limit the number of returned columns:

SPQuery query = new SPQuery();
query.ViewFields = "<FieldRef Name='ID'/><FieldRef Name='Text Field'/><FieldRef Name='XYZ'/>";

Using the Second logic, we achieved our performance a little bit but with the larger lists, this is not at all sufficient so we have created Indexing columns in the back-end SharePoint list or library.

• Indexes

Index columns can speed up access to SharePoint Lists but due to the nature of the implementation of Indices in SharePoint, we have the following limitations:

For every defined index, SharePoint stores the index value for every list item in a separate table. Having a list with let’s say 10000 items means that we have 10000 rows in AllUserData and 10000 additional rows in the NameValuePair table used for indexing.

Queries only make use of the first index column on a table. Additional index columns are not used to speed up database access.

However, with the above steps, we have not achieved the performance as expected so we did some research on how the SharePoint list will take care of the pagination and we found some interesting class SPListItemCollectionPosition.

• SPListItemCollectionPosition

SPListItemCollectionPosition is a server object model type and should be used along with modules executed on the server. This class enables Pagination for Lists inside SharePoint. It stores the state of a page and thus helps in fetching the next page in the dataset. The class can be used along with the SPQuery object. It provides the following advantages:

• Reduced network traffic
• Reduced memory
• Faster result delivery

Let me explain you the complete logic, how we can fetch and display the data on the grid assuming 1000 items in a list and 10 items per page.

protected void ShowDataButton_Click(object sender, EventArgs e)
{
SPQuery query = new SPQuery();
query.RowLimit = 10;
query.ViewFields = "<FieldRef Name=\"Title\" />" + /* Title is LastName column */
"<FieldRef Name=\"FirstName\" Nullable=\"TRUE\" Type=\"Text\"/>";
string listName = "Contacts 1000";
SPList list = SPContext.Current.Web.Lists[listName];

SPListItemCollection collection = list.GetItems(query);

PagingInfo.SavePageInfo(ViewState, collection.ListItemCollectionPosition.PagingInfo);

GridView1.DataSource = collection.GetDataTable();
GridView1.DataBind();
}

Here we are using the SPListItemCollection object to memorize the items fetched. To persist this information between page requests, we are saving it to the ViewState using the PagingInfo class. In this case, the ViewState contains the following data: Paged=TRUE&p_ID=10. It stores the last ID of the item displayed. This ID is required for processing the next button.

Following is the code for the Next Page button functionality.

protected void NextPageButton_Click(object sender, EventArgs e)
{
SPQuery query = new SPQuery();
query.RowLimit = 10;
query.ViewFields = "<FieldRef Name=\"Title\" />" + /* Title is LastName column */
"<FieldRef Name=\"FirstName\" Nullable=\"TRUE\" Type=\"Text\"/>";
string listName = "Contacts 1000";
SPList list = SPContext.Current.Web.Lists[listName];

/* New */

query.ListItemCollectionPosition = PagingInfo.GetNextPagePosition(ViewState);

SPListItemCollection collection = list.GetItems(query);

PagingInfo.SavePageInfo(ViewState, collection.ListItemCollectionPosition.PagingInfo);

GridView1.DataSource = collection.GetDataTable();
GridView1.DataBind();
}

The code takes the Page Position from the View State using the PagingInfo Class.