How to perform a faster search on User list

Question

I'm trying to fetch a user by its custom profile property. The code times out because there are more than 100000 records. So, I wrote a second method, which seems a little better, but maybe because the user was found in the first set itself.

1. I would like to know if there is a better or faster way to get the result.

2. Also, is there a way to get var pagewiseUsers ordered by Descending order of created date, as it will improve the search time for this particular use case.

   public static User GetUserByProfileProperty(string domain, ID profileID, string profilePropertyName, string profilePropertyValue)
   {
     User user = null;
     Item profileItem = GetItemFromCoreDB(profileID);
   
     if (profileItem != null)
     {
       //METHOD 1 - which times out
       user = Domain.GetDomain(domain).GetUsers()
              .Where(x => x.Profile.GetCustomProperty(profilePropertyName) == profilePropertyValue).FirstOrDefault();
   
       //METHOD 2
       int totalUsersCount = Domain.GetDomain(domain).GetUserCount();
       int pageSize = 10000;                
       int totalPages = (totalUsersCount + pageSize - 1) / pageSize;                
   
       for (int pageIndex = 0; pageIndex < totalPages; pageIndex++)
       {
         var pagewiseUsers = Domain.GetDomain(domain).GetUsers(pageIndex, pageSize);
   
         if (pagewiseUsers != null && pagewiseUsers.Count() > 0)
         {
           user = pagewiseUsers.Where(x => x.Profile.GetCustomProperty(profilePropertyName) == profilePropertyValue).FirstOrDefault();
   
           if(user != null)
              break;
         }
       }                    
     }
   
     return user;
    }
   

Answer 1

There is, but none of the ways I know of will go via the standard API.

The "problem" is; while the standard API will allow you to paginate through the user base, it will not allow you to filter based on profile properties. Which, from your code, is what you need here.

This leaves you with a few options.

Option 1, the SQL route

Turns out, even this isn't as straight forward as one would hope. The problem being the way the Property Store is organised. Your first step would be to create enough SQL to actually execute a query against it, which you would then be able to paginate.

• How to get asp.net profile value from MS SQL database using T-SQL?

I would need to paste in the entire article here, which would be too much. But essentially you need to declare a few SQL functions to help you extract profile data from the property store, which you can then use in a SELECT CROSS FUNCTION when selecting your users.

A bit of additional reading:

• https://stackoverflow.com/questions/7315284/sql-aspnet-profile

Option 2, the SQL route with a twist

Ages ago, ScottGu released a SQL Table Profile Provider. Essentially a standard Profile Provider, that relied on a full table for provider properties. The idea being, once you have it in a standard SQL table; JOIN and SELECT becomes easy.

I've never used this myself and I don't even know if it is applicable to current ASP.NET implementations.

Anyway, it's an option to look into.

• ASP.NET 2.0 SQL Table Profile Provider Released Today
• Using the SQL Table Profile Provider in ASP.NET 4 Web Applications

Option 3, indexing

This is what I've normally done. Create a job that, on regular intervals, sweeps up the users and their properties and throws them into an index. SOLR or Lucene or whatever is on hand. Once there, it becomes easy to query, filter, facet and sort your user base - and it just overall a far more appropriate way of querying a large userbase.

Unfortunately, none of the above would be a "quick fix" for where you're at right now. I would recommend taking a closer look at option 3.