Managing Anonymous Users For Better Site Performance
Anonymous identification is an wonderful feature for allowing ASP.NET applications to store personalized information for unauthenticated users; but if you aren't careful, you'll eventually end up with thousands (or even millions!) of unused records clogging your database, hogging resources, and slowing your app to a crawl. Here's a handy way to keep that runaway database under control when using anonymous identification.
Some time ago, I was consulted to do some work on a ASP.NET e-commerce application. Though the site had a few thousand registered members, the aspnet_Users table had grown to nearly one million rows and the database had grown to over 4 GB in size! The site was using anonymous identification, but no effort was being made to cull old anonymous user records. Needless to say, site performance suffered greatly because of this.
How Anonymous Identification Works
The main reason to enable anonymous identification in your application is to allow profile properties to be stored for unauthenticated users, such as display preferences, shopping carts, and the like. It's enabled in the application's configuration file (web.config) as follows:
<system.web>
<anonymousIdentification enabled="true"/>
system.web>
With anonymous identification enabled, a new record is created in the database's users table for each new visitor, and if applicable a new profile is created in the database as well. A unique identifier is also assigned and stored in a persistent cookie in the visitor's browser (by default this cookie is named .ASPXANONYMOUS). Upon subsequent visits the cookie is read, and its unique identifier is used to match the user with the corresponding user and profile records in the database.
The Problem
While anonymous identification serves a useful purpose, you can see what inevitably happens. Remember, a new user record is created for every new unauthenticated visitor. If the same unauthenticated user visits again after the anonymous cookie has expired, another record is created. And of course, when an authenticated user logs out, that user becomes unauthenticated and -- you guessed it -- yet another new record. These records remain in the database permanently. Eventually, the user and profile tables become immense, clogged with thousands (or even millions) of useless records!
What exactly do we mean by "useless?" Well, the users table contains a field called LastActivityDate, which tracks the last time the user visited the site. If that date is past the expiration interval for anonymous cookies (by default, this interval is 100,000 minutes), then the cookie will have already expired on the user's browser, and therefore the corresponding anonymous user record can never be accessed. Thus, we can say with certainty that any anonymous user record for which LastActivityDate < DateTime.Now.AddMinutes(-100000) is a dead record. For a site that's been in use for some time, these expired records will in fact comprise the vast majority of the user and profile tables.
The Solution
Obviously, you need a way to conveniently identify these expired records and delete them. You need to do this regularly, so that the number of records to delete at any one time is relatively small, and can be easily done while the site is running without degrading performance.
A good way to accomplish this is to create an administrative page in your site that lets you manage these anonymous records. To do this, you need to:
- Select all anonymous users whose records have expired, according to the timeout you've set for anonymous cookies. As stated before, this is 100,000 minutes (69 days, 10 hours, 40 minutes), but this number is configurable in the
section of web.config. Order the result by LastActivityDate, so that the oldest records are cleaned up first. - Delete the profiles associated with the selected users.
- Remove these users from any roles they may belong to (though it's not common to assign a role to an anonymous user, it is possible).
- Delete any personalization information associated with the selected users.
- Finally, delete the users.
At the end of this blog post I include a sample solution with a simple page called ManageAnonymousUsers.aspx that accomplishes this. The data access methods are completely self-contained in the code behind, so you can drop this directly into a secured folder in your application, point it to your own connection string, and use it right away.
The code behind for this page is as follows:
using System;
using System.Configuration;
using System.Data.SqlClient;
public partial class ManageAnonymousUsers : System.Web.UI.Page
{
// make sure to place this page in a secured part of your site
// accessible only to administrators.
// if desired, you can specify the field values below from the config file
// rather than hard-coding them here
//change this if you've specified a different timeout
DateTime expireDate = DateTime.Now.AddMinutes(-100000D);
// make sure this number is small enough as to not overtax the server
int defaultNumOfRecordsToDelete = 2000;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
txtNumToDelete.Text = defaultNumOfRecordsToDelete.ToString();
try
{
lblNoOfAnonUsers.Text = GetNumberOfAnonymousUsers().ToString();
lblNoOfExpAnonUsers.Text = GetNumberOfExpiredAnonymousUsers().ToString();
}
catch (SqlException)
{
lblNoOfAnonUsers.Text = "An error occurred while attempting to obtain this value.";
lblNoOfExpAnonUsers.Text = "An error occurred while attempting to obtain this value.";
}
}
}
private int GetNumberOfAnonymousUsers()
{
using (SqlConnection con = new SqlConnection(GetConnectionString()))
{
string sel = "SELECT COUNT (*) FROM aspnet_Users WHERE IsAnonymous = 1";
SqlCommand cmd = new SqlCommand(sel, con);
con.Open();
return (int)cmd.ExecuteScalar();
}
}
private int GetNumberOfExpiredAnonymousUsers()
{
using (SqlConnection con = new SqlConnection(GetConnectionString()))
{
string sel =
"SELECT COUNT (*) FROM aspnet_Users " +
"WHERE IsAnonymous = 1 AND LastActivityDate < @expireDate";
SqlCommand cmd = new SqlCommand(sel, con);
cmd.Parameters.AddWithValue("@expireDate", expireDate.ToShortDateString());
con.Open();
return (int)cmd.ExecuteScalar();
}
}
protected void btnDelete_Click(object sender, EventArgs e)
{
try
{
using (SqlConnection con = new SqlConnection(GetConnectionString()))
{
int numToDelete;
if (radAllExpired.Checked)
numToDelete = GetNumberOfExpiredAnonymousUsers();
else
numToDelete = int.Parse(txtNumToDelete.Text);
// select the anonymous users
string subQuery = String.Format(
"(SELECT TOP {0} UserId " +
"FROM aspnet_Users " +
"WHERE IsAnonymous = 1 AND LastActivityDate < '{1}' " +
"ORDER BY LastActivityDate)",
numToDelete.ToString(), expireDate.ToShortDateString());
// delete profiles for anonymous users
string delProfiles =
"DELETE FROM aspnet_Profile " +
"WHERE UserId IN " + subQuery;
SqlCommand cmdDelProfiles = new SqlCommand(delProfiles, con);
// delete roles for anonymous users
string delUsersInRoles =
"DELETE FROM aspnet_UsersInRoles " +
"WHERE UserId IN " + subQuery;
SqlCommand cmdDelUsersInRoles = new SqlCommand(delUsersInRoles, con);
// delete personalization for anonymous users
string delPersPerUser =
"DELETE FROM aspnet_PersonalizationPerUser " +
"WHERE UserId IN " + subQuery;
SqlCommand cmdDelPersPerUser = new SqlCommand(delPersPerUser, con);
// delete anonymous users
string delUsers =
"DELETE FROM aspnet_Users " +
"WHERE UserId IN" + subQuery;
SqlCommand cmdDelUsers = new SqlCommand(delUsers, con);
con.Open();
cmdDelProfiles.ExecuteNonQuery();
cmdDelUsersInRoles.ExecuteNonQuery();
cmdDelPersPerUser.ExecuteNonQuery();
int result = cmdDelUsers.ExecuteNonQuery();
lblResult.Text = String.Format("{0} records deleted.", result.ToString());
}
lblNoOfAnonUsers.Text = GetNumberOfAnonymousUsers().ToString();
lblNoOfExpAnonUsers.Text = GetNumberOfExpiredAnonymousUsers().ToString();
}
catch (SqlException)
{
lblResult.Text = "An error occurred while attempting to delete.";
}
}
private string GetConnectionString()
{
// you would need to change this to refer
// to your own connection string name if required
return ConfigurationManager.ConnectionStrings["LocalSqlServer"].ConnectionString;
}
}
Implementing in an Existing Application
If you add this functionality to a site that's been around for a while, you may be surprised to find you have a whole lot of expired users! If that's the case, you'll want to be careful about how you approach this issue.
- Do this at a time when traffic to the site is low.
- Don't do this at the same time as other maintenance jobs may be scheduled, like a defrag or backup.
- You'll notice that there is an option to limit the number of records deleted per pass, which you can override in the page. If you have many thousands or even millions of expired rows, you probably don't want to delete them all at once, which is likely to take hours and grind your database to a screeching halt. Deleting a few thousand records at a time is a lot safer.
- Removing so many records will result in heavy defragmentation, so you'll want to run a defrag after you're done.
- Once you've got things under control, use this tool to maintain the user tables on a regular basis.
Managing Anonymous Users Automatically
Another advantage to self-containing the delete functionality in this sample is to allow you to easily convert this to a console application that can be executed on a periodic basis with a scheduled task.
I'd suggest scheduling the task to run daily; or even more often with a very busy site. If you do this, you'll want to experiment with setting the defaultNumOfRecordsToDelete limit and/or the scheduling frequency, so that expired records don't continue to accumulate. You can always use the maintenance tool to keep an eye on things, and to make sure your console app is keeping up.
Conclusion
Anonymous identification is a great ASP.NET feature, but if not properly maintained, it can cause more problems than it solves. Culling expired anonymous users on a regular basis will help keep your database under control and your application running smoothly.
Download the sample solution here.
Subscribe to this blog for more cool content like this!
You've been kicked (a good thing) - Trackback from DotNetKicks.com
Pingback from Link Post Sunday 01/03 | Mr Sun Studios