How to delete a lot of orders in a performant way?

We want to delete old orders from the database. Litium comes with the OrderCleanupScheduler task that can be used for this but it simply executes the sql statement delete dbo.ECommerce_Order where OrderDate < DATEADD(MONTH, {0}, GETDATE()) and OrderStatus in ({1}) which unfortunately takes a very long time to complete and locks the affected tables.

What’s your suggestion on how to delete a lot of orders from a production database without affecting performance?

Litium version: 7.4.2

You can write own scheduled task that deletes an optiumum amount of orders and wait an optimum time before it starts again. For example: 100 orders and wait 15 minutes (have no idea about the number of orders that you want to delete).

There are two problems; that it takes such a long time to delete an order from the database, along with all related records from other tables, and that during that time there are locks on those tables. I guess it might be better to delete orders one at a time using e.g. a loop to have fewer locks but I’m no database expert. What do you think?
And about the time it takes to delete - do you have any suggestions or workarounds on how to speed up the deletions? We have a lot of orders and it takes about three-four seconds to delete one. Edit: It took six minutes to delete ten orders in one of our test databases in the Litium hosting environment.

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.