Performance Optimization: How to Read, Filter, Sort and Enumerate SharePoint User Profiles as Quickly as Possible

In this article, I will show how to read and enumerate SharePoint user profiles and their properties as quickly as possible. It is especially useful when SharePoint User Profile Service contains thousands of user profiles and you need to filter, sort and process them. Of course, you can use UserProfileManager class and the code below to enumerate user profiles. As practice shows, it is extremely slow and when you need to process thousands of user profiles, it can take a while:

using(SPSite site = new SPSite("http://yoursiteurl"))
{
  SPServiceContext servContext = SPServiceContext.GetContext(site);
  var upm = new UserProfileManager(servContext);

  foreach (UserProfile prof in upm)
  {
    //Get property values from prof, for example PrefferedName
    string preferredName = prof[PropertyConstants.PreferredName]
  }
}

I will show how to load data directly from user profiles database. With SQL query, you can filter and sort data about user profiles quickly. You can use the following SQL query to get all user profiles with their properties:

/* Select property values */
SELECT upf.NTName AS Login, pl.PropertyName AS Property, 
        upv.PropertyVal AS Value
INTO #propBag
FROM dbo.UserProfile_Full AS upf INNER JOIN
        dbo.UserProfileValue AS upv ON upf.RecordID = upv.RecordID INNER JOIN
        dbo.PropertyList AS pl ON upv.PropertyID = pl.PropertyID              

/* Transform rows to columns */
SELECT  *
FROM
(
    SELECt Login, Value, Property FROM #propBag
) AS d
PIVOT
(
    MAX(Value)
    -- Properties TO SELECT
    FOR Property IN (AccountName, PreferredName, FirstName, LastName,
                     Title, Department, Office, WorkPhone, WorkEmail,
                     Manager, PictureURL, CellPhone)
) AS piv;

If you execute the first select from this query, it returns data as list of properties and values:

Fortunately, MSSQL provides pivot operator which allows to transform rows to columns. The second part of the query uses pivot operator. Thus, we get results in the following format:

Once we have data in the correct structure, we can add WHERE and ORDER BY operators. You also can add new properties into the query.

If you work with SQL only and you know connection string of your user profiles database, it is enough for you. You can create SQL connection and read data, but I want to implement universal mechanism, which will work for any SharePoint farm. I assume that I don’t know connection string of user profiles database and I need to get it dynamically.

Unfortunately, SharePoint public API doesn’t provide all required methods and I have to use a little bit of reflection to get it working. This is how my C# code looks like:

public void GetProfilesBySqlQuery(string sqlQuery)
{          
  SqlCommand sqlCommand = new SqlCommand(sqlQuery);
  sqlCommand.CommandType = CommandType.Text;  

  BindingFlags bindingFlags = BindingFlags.Instance | BindingFlags.NonPublic);

  //Get User Profile Service Application Proxy object for current web app
  PropertyInfo upAppProxyProperty = UserProfileManager.GetType()
                                    .GetProperty("UserProfileApplicationProxy",
                                                   bindingFlags);                                          

  object upAppProxy = upAppProxyProperty.GetValue(UserProfileManager, null);

  //Get SqlSession object from Service Application Proxy
  PropertyInfo sqlSessionProperty = upAppProxy.GetType()
                                      .GetProperty("ProfileDBSqlSession",
                                                   bindingFlags);
  object sqlSession = sqlSessionProperty.GetValue(upAppProxy, null);

  //Get ExecuteReader method information
  MethodInfo methodInfo = sqlSession.GetType().GetMethod("ExecuteReader", 
                                      new Type[] { typeof(SqlCommand) });

  //Execute query
  using (var dataReader = (SqlDataReader)methodInfo.Invoke(sqlSession, 
                                         new object[] { sqlCommand }))
  {
    while (dataReader.Read())
    {
      //Read data from data reader
    }
  }
}

Let us analyze the structure of internal classes I use. As you see, I use reflection to access properties and to call methods of internal classes. I have to use reflection because there are no public APIs which allow to find current User Profile Service Application.

UserProfileApplicationProxy is an internal class which represents User Profile Application Proxy. It is not possible to access it without reflection. This class has ProfileDBSqlSession property (SqlSession class). SqlSession is an internal class which allows to access SQL database. I use it to access user profiles SQL database. This class has ExecuteReader method which allows to execute SQL query and to get SqlDataReader as result. You can use it as any other SqlDataReader:

//Read property values from data reader
while (dataReader.Read())
{
  //Get column index
  int colIndex = dataReader.GetOrdinal(PropertyConstants.PreferredName);
  //Check if value is null and get string value
  string prefferedName = !dataReader.IsDBNull(colIndex) 
                            ? dataReader.GetString(colIndex) 
                            : string.Empty;
}

Looks good, but it is not the final solution. When we deal with thousands of items, each operation can become a bottleneck for performance. In our case, there are three bottlenecks:

• IsDBNull method
• GetOrdinal method
• GetString method

Each of them takes a little portion of CPU time while executing in the loop. I used dotTrace to estimate execution time for each method. IsDbNull takes much more time than others. To optimize performance, I removed this method from my code and moved check for null into the query. I use COALESCE to replace NULL with empty string:

COALESCE(PreferredName, '') 

To optimize performance of GetString method, I use CONVERT to get nvarchar values. I guess it allows to avoid extra conversions while reading the value:

CONVERT(nvarchar(max), COALESCE(PreferredName, ''))

As a result, I have the following SQL query:

SELECT upf.NTName AS Login, pl.PropertyName AS Property, 
        upv.PropertyVal AS Value
INTO #propBag
FROM dbo.UserProfile_Full AS upf INNER JOIN
        dbo.UserProfileValue AS upv ON upf.RecordID = upv.RecordID INNER JOIN
        dbo.PropertyList AS pl ON upv.PropertyID = pl.PropertyID         

/* Transform rows to columns */
SELECT  CONVERT(nvarchar(MAX), AccountName) AS AccountName, 
    CONVERT(nvarchar(MAX), COALESCE(PreferredName, '')) AS PreferredName, 
    CONVERT(nvarchar(MAX), COALESCE(FirstName, '')) AS FirstName, 
    CONVERT(nvarchar(MAX), COALESCE(LastName, '')) AS LastName,     
    CONVERT(nvarchar(MAX), COALESCE(Title, '')) AS Title, 
    CONVERT(nvarchar(MAX), COALESCE(Department, '')) AS Department, 
    CONVERT(nvarchar(MAX), COALESCE(Office, '')) AS Office, 
    CONVERT(nvarchar(MAX), COALESCE(WorkPhone, '')) AS WorkPhone, 
    CONVERT(nvarchar(MAX), COALESCE(WorkEmail, '')) AS WorkEmail, 
    CONVERT(nvarchar(MAX), COALESCE(Manager, '')) AS Manager, 
    CONVERT(nvarchar(MAX), COALESCE(PictureURL, '')) AS PictureURL, 
    CONVERT(nvarchar(MAX), COALESCE(CellPhone, '')) AS CellPhone
FROM
(
    SELECT Login, Value, Property FROM #propBag
) AS d
PIVOT
(
    MAX(Value)
    -- Properties TO SELECT
    FOR Property IN (AccountName, PreferredName, FirstName, LastName,
                     Title, Department, Office, WorkPhone, WorkEmail,
                     Manager, PictureURL, CellPhone)
) AS piv;

And this is server side code I use to enumerate through the results of SQL query:

//Get ordinals before loop
int colIndex = dataReader.GetOrdinal(PropertyConstants.PreferredName);

while (dataReader.Read())
{
  //I don't check for DB null, because it was done in the sql query
  string prefferedName = dataReader.GetString(colIndex);
}

Please take code in the beginning of this article and replace the while loop with this code.

As you can see, I removed IsDBNull method. I also moved GetOrdinal method outside of the loop. Thus I get ordinal one time and then I reuse it within the loop.

That is all, now you can read, filter and sort user profiles using SQL query. This allows to process thousands of profiles as quick as possible. Moreover, you can use recursive queries to build hierarchy of employees by manager property. With such queries, you can traverse up and down within structure of employees.

Should you have any questions, feel free to comment. CodeProject