I discovered ASP.NET profiles a few years back and found it quick and easy to implement some powerful functionality in my web site. I ended up exclusively using "anonymous" profile properties because I am NOT using ASP.NET membership and wanted to track information and provide personalization for my site's users even though they weren't logged in.
Fast forward 3 years and now I am struggling with keeping the size of my aspnetdb database within the 3 GB limit of my hosting provider. I decided a year ago that I would only keep 6 months of profile data and every month I now have to run the aspnet_Profile_DeleteInactiveProfiles procedure followed by a support request to my host to truncate the database file.
The real downside is now that I have this functionality implemented, the web site relies on this database and there is downtime every time I need to truncate. Today really took the cake - the site was down for more than 12 hours while doing maintenance on aspnetdb and I ended up creating a failover copy of the aspnetdb database just so I could get the web site online again (the original aspnetdb database is still down as I write this).
Last year I rewrote some of the functionality because it was storing a small collection of binary objects and I converted it to store a comma delimeted string of IDs, making it smaller for storage.
I have looked into getting more disk space, but the reality is it simply isn't worth it to have a database that grows by 4 GB every year just to do simple things like keep a list of each user's most recently viewed products, keep track of what category they were in for a "continue shopping" button to take them back, and store the total amount (currency only) that is in their shopping cart. Realistically, less than 5% of the data is reused by repeat visitors to the site, however there is no way to tell in advance which of these users will make it back to access their profile information.
So my question is whether there is a more efficient way to store the profile data so it doesn't take up so much space or if there are alternatives to creating profile database records for every single user for supporting this functionality?
I took a look at the table profile provider, but can someone vouch whether using it will store the data using less disk space than the default provider?
Update:
I did some research and it seems that the nvarchar(max) field is a direct replacement for the ntext fields that are used by the default implementation of the aspnetdb, but will only require half of the disk space according to this article and this one. This means I should be able to create a new copy of aspnetdb, modify the datatype throughout its schema and code, and transfer the data into the new database. That should fix both the way the data is stored and any fragmentation that occurred when the shrink operation was performed.
I have now completed testing and moved this solution into production. At first I thought there would be a problem because Microsoft hard-coded the NText datatype into the stored procedure call inside of the SqlProfileProvider. However, as it turns out SQL Server will implicitly convert an NText parameter to nvarchar(MAX). In short, I didn't have to change anything other than the types on the PropertyNames and PropertyValuesString columns of the aspnet_Profiles table.
The data had to be rewritten to disk to free up the space, but I ended up saving about 30% overall just by changing the datatype.
Another Update:
I also discovered that although I am getting around 700 unique visitors every day, the average number of daily "users" in the aspnet_Users table averages about 4000-5000. I theororized that this was due to the fact that some users are browsing without cookies. I did some testing and discovered that a user won't be created if the profile is not updated, but if you write to the profile a user (and profile) will be created on every request if cookies are not enabled.
I am working on a workaround for this. I am attempting to write the javascript for an AJAX call to a webmethod. In theory, the second request (the AJAX call) should have the .ASPXANONYMOUS cookie present if cookies are enabled, and therefore I can safely write to the profile. The javascript will only be injected into the page if this is the beginning of the session (determined by the Session.IsNewSession property). The user should never be aware of the AJAX call - it is only there to tell the page whether cookies are enabled so it can update the profile.
Of course, every subsquent request can simply check the cookies collection to ensure the .ASPXANONYMOUS cookie is present. Most likely I will create a shared function that can be called both by an AJAX called webmethod and by the Page_Load event directly, and use the IsNewSession property to determine which request to allow to run the cookie check and subsequent profile update.
According to Microsoft, using session to keep track of whether cookies are enabled defeats the purpose (because session depends on cookies). They suggest to persist your AreCookiesEnabled value to a database, but they don't make mention of how you are supposed to keep track of where you kept the value - if cookies are not enabled, how can you link a request to a database record?