Why deleting objects is too slow

[snezhek]

Hi all,

We're using Konakart with large number of products and categories.

There are 562 categories and 10211 of products in database. When we delete categories, this process goes _too slow_, about 5 minutes on good machine. My questions are why and how to speed up this?

(Seems to be trouble is not in konakart but somewhere in Torque ORB?)

[ryan]

Hi,

If you delete a category and choose to delete all of the products within the category, it will delete the category and for each product it will delete, in a transaction :

• Data from the product table


• Data from the product description table


• Data from the product to categories table


• Data from the product attributes table


• Data from the product quantities table


• Any reviews the product may have


• The product from any baskets that it may be in

If you have thousands of products, this will take a while. However, this shouldn't be something that you do on a daily basis and it isn't a performance issue for your customers and so shouldn't really be a big issue for you ?

If you'd like to investigate further you should really start by looking at the resources (CPU, memory) used by your database compared to the ones used by the Java virtual machine in order to decide who is using up the most resources.

-Ryan

[snezhek]

Thank you for your answer.

I've written a PL/SQL script wich performs exactly the same you wrote. It contains almost all checks for values included in tables, not dummy "delete from <table>" without any where condition. And it took 14 seconds to execute.

You are right, this is very uncommon operation in production, but now we're in development mode and do this often (in order to clear dirty products or debug data). So we have to suffer this or call native SQL script.

!Btw, deleting category with its products with KonaKart doesn't delete values from products_to_products table! It seems to be a clear bug.

[ryan]

The deletes I gave you in my post are all done in a single transaction which will obviously make it a lot slower but should guarantee not to leave your DB in a strange state. I'm sure that your script is fine during development.

Thanks for pointing out the missing products_to_products table. We'll add that to the next release.