My musings about .NET and what not

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:

  1. 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.
  2. Delete the profiles associated with the selected users.
  3. 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).
  4. Delete any personalization information associated with the selected users.
  5. 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!

kick it on DotNetKicks.com

shout it on DotNetShoutOut.com

vote it on WebDevVote.com

Bookmark / Share

    » Similar Posts

    1. How to Prevent Profile Properties From Updating a User's Last Activity Date
    2. Defensive Programming, or Why Exception Handling Is Like Car Insurance
    3. Getting Strongly Typed Profile Properties From a Class Library

    » Trackbacks & Pingbacks

    1. You've been kicked (a good thing) - Trackback from DotNetKicks.com

      Managing Anonymous Users For Better Site Performance — December 21, 2008 4:15 PM
    2. Pingback from Link Post Sunday 01/03 | Mr Sun Studios

      Link Post Sunday 01/03 | Mr Sun Studios — May 21, 2009 6:07 PM
    Trackback link for this post:
    http://leedumond.com/trackback.ashx?id=32

    » Comments

    1. Yoann. B avatar

      Hi,

      Great article

      Thanks.

      Yoann. B — December 21, 2008 5:29 PM
    2. trendbender avatar

      good review

      trendbender — December 23, 2008 8:20 AM
    3. Vireh Shah avatar

      Very good article..Very handy..

      Vireh Shah — September 25, 2009 1:21 AM
    4. anaxbaec seo avatar

      Good writting admin. This will be really useful.

      anaxbaec seo — March 11, 2010 7:22 PM
    5. Colin Smith avatar

      Another way would be to set up a SQL job that runs on a scheduled task to do this automatically at least once a day. This is the method we use (tied with SQLmail to email the deletion results to us).

      Colin Smith — May 5, 2010 4:57 AM

    » Leave a Comment