I had an interesting discussion about this with someone today, and I thought I'd share it with you all.
This person had a relatively small site based on TheBeerHouse. His problem was that his database seemed to be growing out of control. He had only about 100 registered users. Yet, his users table contained over 80,000 rows
, and his DB had bloated to over 250 MB!
If you have a BeerHouse-based site, take a look at your users table, and you may see what I'm talking about here.
The reason this happens is that the app uses anonymous identification. You'll notice this in the web.config:
<anonymousIdentification cookieless="AutoDetect" enabled="true"/>
This is required to support the anonymous profiling features which are used in the Theme selector and the ShoppingCart. Of course, this means the app has to create a "user" for everyone who comes to your site. What happens is that, after a while, your DB contains thousands and thousands of junk records. Ouch.
Briefly, this is how it works: whenever someone comes to the site, the system creates an anonymous user and sets a cookie on that user's computer. Whenever the user returns to your site, it gets the cookie and uses it to "hook up" that anonymous user record to that user.
If the user actually registers on the site, the Profile_MigrateAnonymous method in Global.asax will take whatever info is stored in the anonymous profile and switch it to their authenticated profile. It will then delete that anonymous profile. All is good.
But, what happens when a casual user stops by, but never comes back? Or, if they come back, but only after that cookie on their computer has expired? You've now got a permanent junk record clogging up your DB. You can see how this could really add up after a while.
If you look into the actual user table data, you will see a column called IsAnonymous. Notice that most of them are set to "true". Now, look at the column marked "LastActivityDate". For any records with LastActivityDate older than 30 days, that means that the visitor never registered on your site, AND their cookie has already expired. That's an orphan record that you don't need or want hanging around.
If you use anonymous identification, you MUST have a way for finding and deleting all of these junk user records, along with any associated role, personalization, and/or profile data. Otherwise, your DB will just keep inflating indefinitely. Of course, they unfortunately don't mention this in the book, nor does ASP.NET system have a convenient way of doing this. In my opinion, this should have been built into the framework from the get-go. Sadly, it was not.
You need a procedure to find all Anonymous users older than 30 days and delete them, along with their anonymous Profile, Personalization, and Roles information. After some Googling around, I found a good article that contains a DB script that takes care of this:
I hope this helps some folks out.