Performance consideration for DBClean
DBClean can be tuned to gain performance improvement. The CLEANCONF table holds the actual SQL statement that the script references. Sample SQL stored in the CLEANCONF table to clean up the objects can be found at this link|:
For other objects that can be cleaned up using DBClean, review the list at above link. A rule of thumb is to clean up, at the minimum, obsolete tables and objects. Some of the cleanup utility objects are:
- Order
- Address
- Usertraffic
- Member
- Promotion
- Catalog
- Cacheivl
- Scheduler
- Staging
For more information, see this link about Database cleanup utility objects supported by Commerce:
For example, if a user wants to delete guest shoppers who have no orders associated and who has been in the system for more than 10 days, a new object called guest_shopper can be created by inserting the following SQL into the CLEANCONF table and calling DBCLEAN on the object (Example 25-3).
Example 25-3 Sample SQL to delete guest shoppers with DBClean
Deletes guest_shopper ids older than 10days **delete from memberwhere member_id in (select users_id from users where registertype='G'and (days(CURRENT TIMESTAMP) - days(lastsession)) >= 10and (users_id not in (select member_id from orders)) and (users_id > 0))
xxxx