In a customer solution I’ve been working on we use ASP.Net Membership and Profile to store information about users. We use profile data extensively in various reports and listings throughout the solution.
Putting the solution under some regular user activity though, showed some really poor performance when producing reports. Some of these are large reports mind you so I had to go digging to figure out what was going on. I always check SQL Server activity first, looking for waiting processes and locks. And yes, there it was: an exclusive lock on aspnet_Users. Why, we’re only doing reads in these reports!
Further digging into which stored procedures are touching the aspnet_Users table I discovered that the aspnet_Profile_GetProfileProperties actually do an update on the aspnet_Users.LastActivityDate column. This stored procedure does not take any parameter to control this behavior. So a quick solution to the problem was removing the updating part.
Of course, after figuring out what the problem was I suspected that others have figured this out too. Go here for a view of the stored procedure before and after surgery.
