Needed to find this for a client today, so I thought I would post it to the blog and (hopefully) make it easier to find the next time.

With older versions of AC7 i.e. 7.0.3 and earlier, there sometimes is an issue where user maintenance does not run completely through.  As days go on, this problem becomes more pronounced as the ac_Users table continues to grow.  At some point, you’ll start noticing slow site performance and degradation.  Check your ac_Users table and you’ll probably find hundreds of thousands of old user records.

This query will clean those out in one quick swoop.  After that, the user maintenance routine should be able to keep up.  Just change the date in the three query statements for the cutoff date you want.  Also make sure to set the database name for your particular installation at the beginning of the file.

USE <YourDBName>
DELETE FROM ac_Baskets
WHERE UserId IN (SELECT UserId FROM ac_Users WHERE StoreId = 1 AND IsAnonymous = 1
AND (AffiliateId IS NOT NULL OR ReferringAffiliateId IS NOT NULL)
AND (LastActivityDate IS NULL OR LastActivityDate <’June 30, 2009′))

DELETE FROM ac_Wishlists
WHERE UserId IN (SELECT UserId FROM ac_Users WHERE StoreId = 1 AND IsAnonymous = 1
AND (AffiliateId IS NOT NULL OR ReferringAffiliateId IS NOT NULL)
AND (LastActivityDate IS NULL OR LastActivityDate <’June 30, 2009′))

DELETE FROM ac_Users
WHERE StoreId = 1 AND IsAnonymous = 1
AND (AffiliateId IS NOT NULL OR ReferringAffiliateId IS NOT NULL)
AND (LastActivityDate IS NULL OR LastActivityDate <’June 30, 2009′)