Purge eCommerce Basket Addresses Login Performance Issue

  • Updated

This article explains how to remove old eCommerce basket entries by date. On commerce sites, users may create and leave multiple baskets. As a result, the database's basket table may grow very large, causing performance issues when logging into the site.

Backup the database before running any script against it. 

  1. Backup the database.
  2. Copy the following text into sql:  
    declare @dt DateTime
declare @Id bigint
set @dt = '2014-01-01 00:00:00.000';
SELECT basket_id into #tmpBasketIds FROM basket where date_modified < @dt
delete from basket_coupon_tbl where basket_id in(select basket_id from #tmpBasketIds)
delete from basket_item where basket_id in(select basket_id from #tmpBasketIds)
delete from basket where basket_id in(select basket_id from #tmpBasketIds)
drop table #tmpBasketIds
  3. Change the dt variable to delete all baskets modified before that date.
  4. Execute the script on the database.