Thread: Profile Table Maintenance Automation

We have the aspdnsf_MonthlyMaintenance automated to run at the start of the month with a maintenance plan. I didn't notice the stored procedure doesn't include the cleanup of the profiles in the profile table for many months. Thankfully having a obscene amount of rows in this table does effect performance. It just made the database grow large enough for me to wonder why the database had grown so quickly.

To manually clean the table up I used a loop that deleted 10k rows at a time with a delay after each deletion. So the deletion didn't lock the table up and/or timeout during deletion. It ended up taking about 6-7 hrs to run the cleanup but it did the job with out breaking the site during the cleanup.

This leads to my question what are others doing to automate the Maintenance/cleanup of the profile table if at all? I am not too certain about using a loop in a maintenance plan would work with out timeout issues. I am somewhat debating just truncating the table in a maintenance plan at a none peak time but that seems like a heavy handed approach that could lead to other issues. Is there better way to handle this in automated way I'm not thinking of?

Profile table? There is no "profile" table in ASPDNSF.

What version are you on?

Originally Posted by ssgumby

Profile table? There is no "profile" table in ASPDNSF.

What version are you on?

Live wise? It would be 9.0.1.3 Admin Pack. The profile provider was added in version 9. The ASPDNSFProfileProvider class is very much dependent on the profile table being there. Maybe a difference in the version you use?

Ahhh yes, I guess I forgot about V 9.x (maybe ive forced myself to forget!)

So I see that table, I have 1 site live with V 9.0.1.3 since September 2010 and I have just over 1 million records, so yes I can see how this is going to be an issue. Im going to have to research and see how this table is actually being used. Clearly something needs to be done with it, that is poorly thought out.

ok so here are my findings.

You are correct, the stored proc does not clean this table up, I too run the proc on an automated schedule and never knew this table existed (thanks!). So I looked at the admin monthly maintenance screen and there is a Clear Profiles Older Than xxxx ... I selected 60 days, deselected ALL other options as they get cleaned up in my stored proc and then clicke go. It cut the records in this table in over half. I am now down to just over 400k records in that table so ill probably clean it up again. WHen I ran it it literally only took a few seconds to run.

Now, this begs the question. This obviously is a poor design! My site on V9 is VERY low traffic, less than 5k uniques per month. If I had a decent amount of traffic I could not imagine how quickly this table would bloat.

V9, UGH

To put things into perspective when went to check sizes the row count was in the 17 mil range. After doing the while loop I got it down to the 3 mil range. Our sever setup is able to handle such a large size. I didn't even notice for a long while that the table was there till I went to download a backup file to restore on a development sever. The backup file was much larger than I expected so I started looking at table sizes.

I think for now I will go with a separate maintenance plan that does the cleanup and then setup an email notification on the out come. If that doesn't work I will setup a manual process to do so. Unless some new idea comes to pass.

One thing I noticed is the majority of records are anonymous shoppers, probably a lot of bots. You can find these by searching for all records with a customerid of 0. You could probably kill the majority of those records on a regular basis.

I've not done a lot of investigation into the Profile cleanup and the code in ASPDNSFProfileProvider.cs that actually does the row deletion but even when run from the Admin monthly maintenance, it does not appear to delete records when the site is running in a non-US locale.

This seems to be due to locales that use non-US date formatting: dd/mm/yyyy rather than mm/dd/yyyy.

I only noticed this when I was doing some monitoring of a UK site and noticed over 15 million rows in dbo.Profile when I ran a disk usage by table query!

Yeah after looking at the code Profiles it makes sense that the deletion might have an issue with localization. Since the override for DeleteInactiveProfiles uses ToShortDateString to make the sql deletion statement.

After doing some testing locally I found that the ClearAllCustomerProfile method in the customer class added a decent amount Properties to the database with no values. I added some logic to make sure the Property had a value to stop to fix the issue. I'm not sure how much impact the code change will have but hopefully it help to slow down the growth a bit.