In order to maintain persistent shopping carts and wish lists, EP stores session data as customers shop.  Without maintenance, the database tables will grow over time and may lead to performance problems when customers re-visit the site as the system searches through large tables.  This post explains some methods to purge these tables.

Of course, we strongly recommend testing these commands in a test environment before touching your production site.  Also, note that some of these commands may take a long time to execute if the tables are very large.

A session is created whenever a customer shops. If the customer adds items to a cart or wishlist, the session creates a cart with the associated item(s).  If the customer is identified by cookie or sign-in, the session will reference the customer ID; if not, the session customer ID will be null because the customer is not known.  The database works as follows:

Items are linked to carts by a SHOPPING_CART_UID (items contain a reference to the shopping cart primary key)

Carts are linked to sessions by a GUID (reference to session primary key)

Sessions are linked to customers by a CUSTOMER_ID (reference to customer primary key) if the customer is identified; otherwise CUSTOMER_ID is null

Because every visit creates a session, the TCUSTOMERSESSION table is the largest and may have sessions with or without known customer ID and with or without a cart.

There are a couple ways to purge these tables.  The easiest is simply to delete all entries in the TCUSTOMERSESSION table that have no associated shopping cart or items (i.e. customer browsed but did not add anything to a cart or wishlist).  In SQL, this means deleting all TCUSTOMERSESSION records with a GUID that is not found in the TSHOPPINGCART table.  You should add a time parameter to delete only sessions older than x days - that way you don't delete active or relatively recent carts.  This should get rid of the majority of entries. Look at the data or pull a count before deleting to see how it will affect your store.  The basic SQL looks like this:

delete from DB.TCUSTOMERSESSION where GUID not in (select GUID from DB.TSHOPPINGCART);

Another more aggressive way is to delete all TCUSTOMERSESSION records where the CUSTOMER_UID is NULL.  This deletes all carts created for unidentified shoppers (whether or not they are empty), but risks orphan carts that have to be cleaned up.  The SQL is:

delete from DB.TCUSTOMERSESSION where CUSTOMER_UID is null;

This clears out all the sessions for unidentified shoppers, but will likely leave orphan carts in TSHOPPINGCART.  The way to delete these is to first delete all items from all orphaned carts BEFORE deleting the carts, and then to delete all carts whose GUID is not in the TCUSTOMERSESSION table.  The basic SQL would look like below; as you can see, the end of the first command is the same as the second command because first we?re searching for ?sessionless? carts to delete their items and then we?re deleting the sessionless carts themselves.

delete from DB.TCARTITEM where SHOPPING_CART_UID in (select UIDPK from DB.TSHOPPINGCART where GUID not in (select GUID from DB.TCUSTOMERSESSION));

and then

delete from DB.TSHOPPINGCART where GUID not in (select GUID from DB.TCUSTOMERSESSION);

Note that these two methods delete different entities: one deletes sessions that never had carts, the other deletes sessions with unidentified shoppers (that may or may not have carts) and then deletes the orphaned carts.

Finally, you can delete the (relatively few) carts that had no items in them:

DELETE FROM DB.TSHOPPINGCART WHERE UIDPK NOT IN (SELECT SHOPPING_CART_UID FROM DB.TCARTITEM);

These can be done on a running store, but remember to add a time parameter so you don't delete current carts that customers are planning to buy.  And remember to test this in your test environment first!