Number of Carts getting out of control

If you do not carefully plan for cleaning up abandoned carts (Forum post), the number of carts will quickly get out of control. It is highly recommended to have the scheduled job for cleaning up old carts execute on a daily schedule (RemovedExpiredCartsJob). In this short blog post, I will guide you through the different commands you need to run to delete directly in the database. While it is possible to do this with the API, as this forum post shows (http://world.episerver.com/Forum/Developer-forum/EPiServer-Commerce/Thread-Container/2012/6/How-can-we-clear-baskets-that-have-been-expired/), it is much faster to delete with SQL directly.

declare @from datetime = CONVERT (date, GETDATE()-500)
declare @to datetime = CONVERT (date, GETDATE()-10)

declare @ordergroupids TABLE 
( 
    OrderGroupId INT
)

insert into @ordergroupids 
select top 10000 ObjectId from OrderGroup_ShoppingCart
where Created between @from and @to
order by Created asc

The SQL statement above creates a temporary table and fills it with 10000 cart ids that are between 10 and 500 days old

There will be a need for several ids to correctly remove carts. The different identifiers are collected and saved in temporary tables.

declare @orderformids table(OrderFormId INT)
insert into @orderformids
select OrderFormId from OrderForm where OrderGroupId in (select * from @ordergroupids)

declare @lineitemids table(LineItemId int)
insert into @lineitemids
select lineitemid from LineItem where OrderGroupId in (select * from @ordergroupids)

declare @shipmentids table(ShipmentId int)
insert into @shipmentids
select ShipmentId from Shipment where OrderGroupId in (select * from @ordergroupids)

declare @paymentids table(PaymentId int)
insert into @paymentids
select PaymentId from OrderFormPayment where OrderGroupId  in (select * from @ordergroupids)

declare @orderaddressids table(OrderGroupAddressId int)
insert into @orderaddressids
select OrderGroupAddressId from OrderGroupAddress  where OrderGroupId  in (select * from @ordergroupids)

Finally you can delete from the different related tables. Note! Depending on different payments in use, you might need to delete from other OrderFormPayment_[Type] tables.

--Delete shipments
Delete from ShipmentEx_Localization where ObjectId in (select * from @shipmentids)
delete from ShipmentEx where ObjectId in (select * from @shipmentids)
delete from ShipmentDiscount where OrderGroupId in (select * from @ordergroupids)
delete from Shipment where OrderGroupId in (select * from @ordergroupids)

--Delete payments (payments may have been registered, but not completed)
delete from OrderFormPayment_Other_Localization where ObjectId in (select * from @paymentids)
delete from OrderFormPayment_Other where ObjectId in (select * from @paymentids)
delete from OrderFormPayment where OrderGroupId  in (select * from @ordergroupids)

--Delete addresses
delete from OrderGroupAddressEx where ObjectId in (select * from @orderaddressids)
delete from OrderGroupAddressEx_Localization  where ObjectId in (select * from @orderaddressids)
delete from OrderGroupAddress  where OrderGroupId  in (select * from @ordergroupids)

--Delete line items and related tables
delete from LineItemDiscount where LineItemId in (select * from @lineitemids)
delete from LineItemEx_Localization where ObjectId in (select * from @lineitemids)
delete from LineItemEx where ObjectId in (select * from @lineitemids)
delete from LineItem where OrderGroupId in (select * from @ordergroupids)

--Delete order form and related tables
delete from OrderFormDiscount  where OrderFormId in ( select * from @orderformids)
delete from OrderFormEx_Localization where ObjectId in ( select * from @orderformids)
delete from OrderFormEx where ObjectId in ( select * from @orderformids)
delete from OrderForm where OrderGroupId in (select * from @ordergroupids)

--Delete shopping cart
delete from OrderGroup_ShoppingCart_Localization where ObjectId  in (select * from @ordergroupids)
delete from OrderGroup_ShoppingCart where ObjectId  in (select * from @ordergroupids)

--Delete order group
delete from OrderGroup where OrderGroupId in (select * from @ordergroupids)

Remember to activate the scheduled job after cleaning up to prevent things from getting out of control again.

Quick warning / Disclaimer

The scripts have been used in a running site and did remove 100 000+ carts, but I don't take any responsibility for any issues caused by its usage.

I strongly recommend trying it out in a test environment and use a transaction to be able to roll back.

If you spot an issue let me know!