One of the new features in DotNetNuke 6.2 is the Member Directory. During the implementation of this new feature, we ran into some stumbling blocks trying to retrieve user info and user profile information. The issue wasn’t retrieving the necessary information, it was the speed at which the queries were performing at.
What We Introduced
In order to support the Member Directory the queries we used to search for users and user profiles needed a speed boost. In doing so we created two new stored procedures to accomplish what we needed; namely GetUsersBasicSearch and GetUsersAdvancedSearch.
We introduced API to expose these new stored procedures:
public IList GetUsersAdvancedSearch(…)
public IList GetUsersBasicSearch(…)
These methods are found in DotNetNuke.Entities.Users.Internal namespace. These new stored procedures eagerly load profile properties whereas some pre-existing methods such as GetUsers(…), GetUsersByEmail(…), GetUsersByUserName(…), just to name a few, lazy loaded profile properties.
An analysis was done on a retrieval of a filtered list of 10 users, which required 31 database calls using the existing stored procedures. Using the new stored procedures reduced the number of calls from 3n+1 down to 2n+1.
The Challenge of Working With De-Normalized Data
GetUsersAdvancedSearch
Working with a large set of users becomes tricky when returning all the corresponding profile properties. Since the profile properties are de-normalized we needed to do some database pivoting and joining in order to return one nice dataset of users. The question here was when to do this pivoting and joining? We also needed to satisfy the ability to search on any given profile property, user property, or a conjunction of both.
Creating a view would solve this, right? Well not in this case… It was investigated of course, but it was determined since the profile properties are defined by the end user – profile properties can be created/deleted/modified there was no way in which we could create a view to accommodate the dynamic profile properties.
By far the easiest way to accomplish this was to pivot and join everything first, the do our queries against the one large table of user info and user profile properties. This was very straight forward to implement a stored procedure for. With a small set of users, this was fine and dandy, but once we get a significant number of users, this was not very efficient to say the least, due to all the unnecessary pivoting.
In general SQL queries work great when they are dealing with long and skinny tables, but when tables start to become very wide, performance starts to take a hit.
To give you an idea of how big a difference it makes to pivot at the very end here are some results from our testing:
|
Number of Users
|
Return 10 Users - Pivot First
|
Return 10 Users - Pivot Last
|
|
4000
|
~ 40 seconds
|
~ 1 second
|
|
8000
|
~ 1 minute 19 seconds
|
~ 1 second
|
|
50000
|
~ 8 minutes 31 seconds
|
~ 1 second
|
So the idea here was to do the pivoting at the very end, when we have our matching users – thus eliminating unnecessary pivoting. Sound simple? I agree! But it wasn’t…. Having to satisfy the ability to search on the user info or the profile properties prior to pivoting was a tricky thing to do. I won’t go into further details, other than to say it’s a relatively hairy stored procedure.
The End Result
We have introduced two new performant ways to search for users and and their corresponding profiles properties. Feel free to test drive them and add them to your arsenal for developing unruly awesome modules! =)
