Views: 4266
Number of votes: 3
Average rating:

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!

Mar 19, 2015

K Khan
(By K Khan , 3/19/2015 7:29:49 PM)

To reduce the impacts on Live Site, Can be done through a scheduled job by reducing the number of entries.
Regards
/K

Petter Sørby
(By Petter Sørby, 3/19/2015 10:33:53 PM)

@K. Kahn: Do you mean using the sql as a stored procedure and execute it on the SQL or use the EPiServer Scheduled Job?

I would preferred to use the scheduled job (API), but it uses a severe amount of server resources if there are many shopping carts. I would use the script if I had an enormous number of old shopping carts.

Quan Mai
(By Quan Mai, 3/20/2015 3:07:21 AM)

There is a built-in scheduled job for clearing expired carts and should be enabled/run periodically- we would recommend to use API:s, use direct SQL should be considered as the last resort :)

Arve Systad
(By Arve Systad, 3/22/2015 5:34:53 PM)

I did a very similar thing (basically just did a few of them with raw SQL, and let the built in sproc do the rest, since that was fast enough).

This could be a possible future change to the job though, simply to speed it up and make it usable when you have a lot to clean up. I had carts with millions of shipments, and the deletion of one single cart went down from 4++ hours and crashing to less than a minute with success.

Thomas Pettersen
(By Thomas Pettersen, 4/14/2015 3:01:06 PM)

In general, to make database deletions, updates and inserts to go faster, is to disable indexes while the job is running. (This must be done with caution, and preferably while the site is down).

https://msdn.microsoft.com/en-us/library/ms177456.aspx

Arve Systad
(By Arve Systad, 9/7/2015 12:08:43 PM)

To be fair, this job seems to have so much issues that it should be completely remade from scratch as the official built-in-job. It's painfully slow and it doesn't report about errors, and it seems to be frustrating a lot of people.

We're constantly running into stuff with it. Doing a simple "SELECT COUNT (*) from OrderGroup_ShoppingCart WHERE Modified < '2015-09-01'" (which is 7 days ago at this time) gives me ~25000 carts, while the job only deleted about 60, giving no errors. I haven't even bothered to find out exactly why. And so we're spending time fixing something that "is already solved" in a product. Which costs our customers money they don't want to pay, since they have already paid for the product. So the frustration grows..

I kinda hope someone with power reads this and can forward it. Make existing stuff just work before adding new features.

Please login to comment.