New Community Website

Ordinarily, you'd be at the right spot, but we've recently launched a brand new community website... For the community, by the community.

Yay... Take Me to the Community!

The Community Blog is a personal opinion of community members and by no means the official standpoint of DNN Corp or DNN Platform. This is a place to express personal thoughts about DNNPlatform, the community and its ecosystem. Do you have useful information that you would like to share with the DNN Community in a featured article or blog? If so, please contact .

The use of the Community Blog is covered by our Community Blog Guidelines - please read before commenting or posting.

6.2 Speed Boost: Searching Users


6_2_speed_thumb37One 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


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


~ 40 seconds

~ 1 second


~ 1 minute 19 seconds

~ 1 second


~ 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! =)



Comment Form

Only registered users may post comments.


Aderson Oliveira (22)
Alec Whittington (11)
Alessandra Davies (3)
Alex Shirley (10)
Andrew Hoefling (3)
Andrew Nurse (30)
Andy Tryba (1)
Anthony Glenwright (5)
Antonio Chagoury (28)
Ash Prasad (37)
Ben Schmidt (1)
Benjamin Hermann (25)
Benoit Sarton (9)
Beth Firebaugh (12)
Bill Walker (36)
Bob Kruger (5)
Bogdan Litescu (1)
Brian Dukes (2)
Brice Snow (1)
Bruce Chapman (20)
Bryan Andrews (1)
cathal connolly (55)
Charles Nurse (163)
Chris Hammond (213)
Chris Paterra (55)
Clint Patterson (108)
Cuong Dang (21)
Daniel Bartholomew (2)
Daniel Mettler (181)
Daniel Valadas (48)
Dave Buckner (2)
David Poindexter (12)
David Rodriguez (3)
Dennis Shiao (1)
Doug Howell (11)
Erik van Ballegoij (30)
Ernst Peter Tamminga (80)
Francisco Perez Andres (17)
Geoff Barlow (12)
George Alatrash (12)
Gifford Watkins (3)
Gilles Le Pigocher (3)
Ian Robinson (7)
Israel Martinez (17)
Jan Blomquist (2)
Jan Jonas (3)
Jaspreet Bhatia (1)
Jenni Merrifield (6)
Joe Brinkman (274)
John Mitchell (1)
Jon Henning (14)
Jonathan Sheely (4)
Jordan Coopersmith (1)
Joseph Craig (2)
Kan Ma (1)
Keivan Beigi (3)
Kelly Ford (4)
Ken Grierson (10)
Kevin Schreiner (6)
Leigh Pointer (31)
Lorraine Young (60)
Malik Khan (1)
Matt Rutledge (2)
Matthias Schlomann (16)
Mauricio Márquez (5)
Michael Doxsey (7)
Michael Tobisch (3)
Michael Washington (202)
Miguel Gatmaytan (3)
Mike Horton (19)
Mitchel Sellers (40)
Nathan Rover (3)
Navin V Nagiah (14)
Néstor Sánchez (31)
Nik Kalyani (14)
Oliver Hine (1)
Patricio F. Salinas (1)
Patrick Ryan (1)
Peter Donker (54)
Philip Beadle (135)
Philipp Becker (4)
Richard Dumas (22)
Robert J Collins (5)
Roger Selwyn (8)
Ruben Lopez (1)
Ryan Martinez (1)
Sacha Trauwaen (1)
Salar Golestanian (4)
Sanjay Mehrotra (9)
Scott McCulloch (1)
Scott Schlesier (11)
Scott Wilkinson (3)
Scott Willhite (97)
Sebastian Leupold (80)
Shaun Walker (237)
Shawn Mehaffie (17)
Stefan Cullmann (12)
Stefan Kamphuis (12)
Steve Fabian (31)
Steven Fisher (1)
Tony Henrich (3)
Torsten Weggen (3)
Tycho de Waard (4)
Vicenç Masanas (27)
Vincent Nguyen (3)
Vitaly Kozadayev (6)
Will Morgenweck (40)
Will Strohl (180)
William Severance (5)
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out