Performance. It’s a very simple concept. When you write an application or build a website, it needs to do what the user requests in a timely fashion. It also follows that when you are building an application, all efforts should be made to make sure that it performs in the most efficient manner for the user, since without users, there would be no need for your applications, and with no need for your applications, you wouldn’t have a job, at least not one in the realm of software development. Seems pretty simple, right? It definitely does, in theory, but in practice, there are often some pretty big roadblocks to performance. In my experience, the biggest roadblock is software developers ourselves. I’m going to talk about a few specific experiences I’ve had where developers have been the biggest roadblock to application performance and how that was overcome. In all of these cases, all of the applications are web-based, but similar (if not the same) rules should apply to standard desktop applications. This post is the first in a multi-part series that I will be working on over the next few weeks. I will update it with links to the other posts as they become available.
Reusing Code is Not Always the Best Idea
Code reuse is a very important aspect of software development. It saves coding time, minimizes errors and lets us focus our brainpower on more unique and difficult problems an application provides. However, sometimes reusing code can be a big hindrance to performance and really shouldn’t be done. For an example of this, I was recently working on some custom modules for a DotNetNuke website. These modules used the user object available in DotNetNuke (UserInfo). They provided means to list site members and displayed custom user property fields in a sortable table. For custom properties, DotNetNuke uses a normalized table structure: Users contains basic user information, ProfilePropertyDefinition defines the profile properties and UserProfile maps individual property values to a user. Here’s the table design for DotNetNuke v.5.2.0:
This works great and lets you have any number of custom properties for the user. However, the performance for accessing these properties when dealing with potentially hundreds or thousands of users is very poor. When I was building the GridView to display a table with the members’ names, email addresses, and several custom profile properties, this is an example of the code that I was using:
UserInfo user = e.Row.DataItem as UserInfo
if (user != null)
{
string maidenName = user.Profile.GetPropertyValue("MaidenName");
}
With 100 users, it was taking approximately 10 seconds to load (very approximate, I didn’t do any formal timing). It also didn’t help that due to either a bug or a feature of DotNetNuke (not sure which yet) and the method that I was using to load the users, I had to actually load the profile for the user twice: once when the user is loaded (portalID is null) and once to load it with the correct portalID. Also, since I was allowing sorting on some of these custom properties, I had to do the sorting on the webserver. This code took me a matter of minutes to write, which is the huge advantage of code reuse, but obviously, a 10-second load time for each page view was completely unacceptable, especially since I was going to have to be dealing with significantly more than 100 users. So I immediately took to rewriting the code and creating custom data accessors. I created a custom SQL function to return user profile properties based on the user ID and the profile property name, and then created a single stored procedure that retrieves all of the information that I needed for this table:
ALTER PROCEDURE [dbo].[Nexus_MemberList_GetUsersByRoleId]
@PortalId int,
@RoleId INT
AS
SELECT FirstName, LastName, Email, MaidenName, StreetAddress, City, [State], PostalCode, Country, Website, Facebook, IsDeceased,
CASE WHEN LEN(ClassName) = 0 THEN ClassYear ELSE ClassName END AS ClassSort,
CASE WHEN LEN(ClassName) = 0 THEN ClassYear ELSE l.Text END AS Class
FROM (
SELECT
u.FirstName,
u.LastName,
u.Email,
dbo.GetProfilePropertyValue(@PortalId, 'MaidenName', u.UserId) AS MaidenName,
dbo.GetProfilePropertyValue(@PortalId, 'ClassName', u.UserId) AS ClassName,
dbo.GetProfilePropertyValue(@PortalId, 'ClassYear', u.UserId) AS ClassYear,
dbo.GetProfilePropertyValue(@PortalId, 'Street', u.UserId) AS StreetAddress,
dbo.GetProfilePropertyValue(@PortalId, 'City', u.UserId) AS City,
dbo.GetProfilePropertyValue(@PortalId, 'Region', u.UserId) AS [State],
dbo.GetProfilePropertyValue(@PortalId, 'PostalCode', u.UserId) AS PostalCode,
dbo.GetProfilePropertyValue(@PortalId, 'Country', u.UserId) AS Country,
dbo.GetProfilePropertyValue(@PortalId, 'Website', u.UserId) AS Website,
dbo.GetProfilePropertyValue(@PortalId, 'FacebookPage', u.UserId) AS Facebook,
CASE WHEN dbo.GetProfilePropertyValue(@PortalId, 'IsDeceased', u.UserId) = 'True' THEN 1
ELSE 0 END AS IsDeceased
FROM dbo.UserPortals AS UP
RIGHT OUTER JOIN dbo.UserRoles UR
INNER JOIN dbo.Roles R
ON UR.RoleID = R.RoleID
RIGHT OUTER JOIN dbo.Users AS U
ON UR.UserID = U.UserID
ON UP.UserId = U.UserID
WHERE ( UP.PortalId = @PortalID OR @PortalID IS Null )
AND (UP.IsDeleted = 0 OR UP.IsDeleted Is NULL)
AND (R.RoleId = @RoleId)
AND (R.PortalId = @PortalID OR @PortalID IS Null )
) AS a
LEFT OUTER JOIN Lists l
ON l.ListName = 'ClassName'
AND l.Value = a.ClassName
ORDER BY LastName, FirstName
Yes, it is very specific to this module and the specific fields it uses, and I will need to recompile and rerelease the whole thing should updates need to be made, but it really was that way already, since the .Net code using the data made reference to specific custom fields to handle sorting and display anyways. And with this change and the .Net code changes (below), I was able to significantly improve performance, from 10 seconds for 100 members displayed to under 2 seconds for up to 1000 members displayed. In fact, with these changes, sorting and paging (and filtering) on the webserver was still a viable option, but I can now more easily move it to the SQL server should I need to because even though it performs well with 1000 users, performance will definitely degrade as more members are added. This code took me a while longer to write and test (probably about an hour or two) and it is definitely not something I will be able to reuse later, but it meets the need of requirement – displaying a list of members to the website visitor in a timely fashion. There was no way that I could find to show this same data in the same manner (allowing sorting, filtering and varying page sizes) with reusable code that would perform well, so reusable code no longer became a viable option. In this case, following a (theoretically) poor programming practice was the best way to go.
Updated .Net Code:
public class Member
{
public string FirstName { get; set; }
public string LastName { get; set; }
public string MaidenName { get; set; }
public string Class { get; set; }
public string ClassSort { get; set; }
public string StreetAddress { get; set; }
public string City { get; set; }
public string State { get; set; }
public string PostalCode { get; set; }
public string Country { get; set; }
public string Email { get; set; }
public string Website { get; set; }
public string Facebook { get; set; }
public bool IsDeceased { get; set; }
}
Member user = e.Row.DataItem as Member
if (user != null)
{
string maidenName = user.MaidenName;
}
Clearly, the actual usage of the objects involved is not altogether that different from what was done before, but the underlying code is significantly different on many levels. My new stored procedure directly returns all of the properties in a single call for the users, rather than having to make a separate SQL call for each user returned in order to get back all of the profile data needed. You can easily see how this could get out of hand as the number of users are increased. There's also a slight performance increase when using a property accessor versus accessing a collection property's values via a key. The performance is probably negligible on a scale that we're dealing with here, but as hundreds become thousands, then it can become a very big deal.
Conclusion
Obviously, there are more performance optimizations that could be made or at least tested (e.g. LINQ), but at the very least, I got performance to an acceptable level in this situation. I also didn’t really run into a roadblock here, since I was working alone on this module, but imagine if I were working on a team where I was building the front-end for the module only (or even if I was alone on this and was a much more stubborn programmer with regards to code reuse). The developer that wrote the user objects for this project could very well balk at being forced to write completely new, very specific data access code for this one individual module. In fact, the next two parts to this series will go into that aspect a little bit more, since this fits in with both of the next topics: Understanding Your Target Platform and Audience, and Normalization Gone Mad. Be sure to check those out in the next few weeks when they become available as well.
Posted to Blog
