AI OnAI Off
Deleting catalog == super slow
Actually, I just tried just that (DELETE FROM dbo.Catalog), and it seemed to work super fine. After spending ~2.5 hours doing about 20% of the work with deleting "the proper way", it did the remaining work in about 4 minutes. I know it's not a safe and sound way to do things, but everything seems clean to me.
Sep 15, 2017 12:08
It's not as simple as that - because some of the references are not fully "cascade". Also we have version, properties and especially cache from application level.
We however might speed thing up (at least I hope) - if you contact developer support service and provide the catalog so we can look into it.
Sep 15, 2017 13:00
It seems like all versions and properties are cleared out with my statement too, to me.
The application level cache should be doable though. Worst case with a reboot of the app pool, forced from the app itself.
As of now, everything hangs completely while deleting the catalog etc. so nearly anything is better than how it works today.
Sep 15, 2017 13:11
I suspect there might still be thing left in database. Let me be clear: yes, you can do that, but it's not something we support or recommend. So if anything happens, you're on your own.
From framework perspective, no we can't do that. But we can make the deletion faster (we did make it 25% faster in quite recent version) - but we will need to know which is the bottleneck.
Sep 15, 2017 14:02
We're doing complete re-imports from inRiver PIM once in a while, and deleting our catalog as a preparation for this is a pain. We have ~30k catalog entries, each with ~200 prices, ~100 inventory-lines and a bunch of associations between all of them, under ~250 catalog nodes.
While deleting (either just catalog nodes, or the whole catalog in one big bang), I observe the Activity Monitor on our SQL server, and a lot of large SELECT queries show up while deleting. We spend an insane amount of time doing this (at least an hour with timeouts, partial deleting and new attempts). Could probably be done faster if we did it more systematically through the catalog APIs, but still. Should be doable from the GUI without this incredibly unstable/unreliable experience.
Is there anything in the way of making "Delete catalog" be a single "DELETE FROM dbo.Catalog WHERE Id = X" and let the database just cascade this all the way down? AFAIK, this should be way, way faster.