Click here to Skip to main content
Click here to Skip to main content

Performance optimization: How to read, filter, sort and enumerate SharePoint user profiles as quick as possible.

, 14 May 2014
Rate this:
Please Sign up or sign in to vote.
In this article I will show how to read and enumerate SharePoint user profiles and their properties as quick 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 UserProfileM
In this article I will show how to read and enumerate SharePoint user profiles and their properties as quick 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 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 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 not public API which allows 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 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 result I have 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 thought 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 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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Anton Khritonenkov
Technical Lead
Russian Federation Russian Federation
Microsoft certified SharePoint expert with more than 5 year of IT experience. My priority is the design and development of simple, convenient and flexible products.
 
Specialties: SharePoint 2010/2013, Office 365, Project management, Business Intelligence, ASP.NET, C#, Silverlight (MVVM), Windows Workflow Foundation, BPMN, XSL, JavaScript, CSS, MS SQL 2005-2012.
Follow on   Twitter

Comments and Discussions

 
SuggestionNOLOCK hint in SELECT statements PinprofessionalGiorgio Arata19-May-14 4:42 
GeneralRe: NOLOCK hint in SELECT statements PinmemberAnton Khritonenkov19-May-14 19:10 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.140721.1 | Last Updated 14 May 2014
Article Copyright 2014 by Anton Khritonenkov
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid