Performance issues when working with lists using SPFieldUser and large amount of items

On a SharePoint 2013 intranet project I have been working on, we got a report that a web part had started to perform bad after a while of use. The web part presents a personalized list of tools to the end-user, and uses a SPList to store the data. This list is quite simple and includes these fields:

The code is not very complicated either. It executes a SPQuery against the list with a filter on “Groups”, and iterates through the result. On each item in the result, it checks if the user have selected the tools. The user can customize their available links through a form listing all available links, also personalized to the user based upon a group membership.

This solution performed acceptable during development and test, and no apparent issues with performance at the time. Just as a note, load testing was not a part of testing in this project. After a short while in production, end-users experienced a degradation in performance. An analysis detected and an issue with the code retrieving the personalized tools in the web part.

Analysis and solution

To measure the performance of the solution, I used SharePoint’s built in Developer Dashboard. Each time I help with performance issues in solutions, this tool almost never been used during development. The tool first appeared in SharePoint 2010, and even better in 2013. It was easy to see that both execution time and SQL queries went sky high when the solution grew with larger amounts of items and values in the list. This is a clear indication that the code behind the web part was not able to scale very well over time, and a code refactor needed.

PowerShell snippet to enable Developer Dashboard

[code language="powershell"]

$service = [Microsoft.SharePoint.Administration.SPWebService]::ContentService
$addsetting =$service.DeveloperDashboardSettings
$addsetting.DisplayLevel = [Microsoft.SharePoint.Administration.SPDeveloperDashboardLevel]::OnDemand
$addsetting.Update()

[/code]

Before optimalization

This code is a snippet from within a foreach-loop returning all the SPListItem’s in the result. For each of the items, a SPFieldUserValueCollection is initialized and looped through, using LINQ in this example. The performance issue with the extreme number of SQL queries is originating from this collection and the iteration through it.

Performance

Code

[code language="csharp"]
var users = listitem[“Users”];
var userValueColl = new SPFieldUserValueCollection(web, users);
if ((userValueColl.Find(u => u.User.LoginName.ToString(CultureInfo.InvariantCulture).Contains(SPContext.Current.Web.CurrentUser.LoginName)) != null))
{
    // TODO
}
[/code]

After optimalization

The code was using regular methods in the SharePoint API to check if the user existed, and I did not find any other recommended methods to retrieve the users. The issue with the first approach was that each iteration through the SPFieldUserValueCollection fires a separate SQL query to lookup the user data. With my testdata of 100 items in the list and 250 users in each user field, this became several thousand unnecessary queries every time the user views the page. Instead of using the SPFieldUserValueCollection, I ended up with a check against the raw text data in the field value. The only thing that is important to remember when checking the field value, is to identify the user the Site Collection relative User ID must be used. The User ID is located in the AllUsers collection in the SPWeb.

Performance

Code

[code language="csharp"]

var users = "#" + listitem[“Users”]; // Append a "#" to make it possible to do pattern matching for user id in string
var currentUserId = web.AllUsers[SPContext.Current.Web.CurrentUser.LoginName].ID;
var searchPattern = string.Format("#{0};", currentUserId);
if (!users.Contains(searchPattern)) continue;
[/code]

Summary

Refactoring to simple string checking, instead of “correct” use of the possibilities of the API, removed the performance bottleneck. The web part now scales much better with both load and larger amounts of data. In my example, I managed to reduce the number of SQL queries by 65 times (!). Definitively a well couple of hours spent J

From a second perspective, with the knowledge of the performance issues by storing large amounts of values in a SPFieldUser field, I would consider a different approach to storing user values for this use later.