Pragmatically I don't think it is impossible to clone those table and move the old data there (it's not a very trivial task mind you). However I am a bit surprised to hear that rebuild the OrderNote index takes that much time. sure if it is the first time maybe, but afterward you should need to reorganize (as orders are cumulative and should not require exceeding deletes)
Devil advocate, but as a visitor I'd be very unhappy to not see my order history. of course, the older the order is, the less I'd need it, but that's something you might want to consider if you archive order information
P/S i happen to have a sample db with 35M rows in OrderGroupNote, I checked and the clustered index is a just a little more than 1GB. There might be something special about your table?
Thanks Quan, will check the rebuild script that we use.
I guess my underlying question here is:
It seems there is no built-in support in Optimizely Commerce for running a business for a long time.
By design, these tables will grow bigger and bigger and become slower and slower. It was even worse before, when the cart competed with the same storage. The Serializable cart saved us that time.
But a similar solution would be needed to preserve historical order data and move it out. To provide a "constant" performance over time.
We are considering a custom solution to jsonify everything (so that it can be retrieved) instead of cloning all tables (there are probably 20+ something tables involved), but I'd much rather prefer if there was support OOTB for this.
that is a good question and actually something I have thoughts from time to time. Agreed that it is not optimal but as order information is mostly appending, and with proper indexes (which supposed to have O(1) access), it will keep you good for some time.
I will discuss with our PM to see if that is something we should invest in.
Good news for you Johan, our PM is interested in looking into this, but priority needs to be determined. Would you mind creating an idea in Aha! and see how many votes it get? Higher votes mean we could spend time sooner on this
What's the best practice recommendation to deal with historical order data?
We have a customer that has accumulated orders since 2017.
Table stats:
This also affects the indexes:
Defragging the ordernote index alone takes 25 minutes.
I see two alternatives here:
Anyone has experience with similar order volumes, and if so, how did you handle it?