Try our conversational search powered by Generative AI!
AI OnAI Off
How to deal with historical order data
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)
Edited,
Feb 28, 2024 10:14
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
Feb 28, 2024 10:19
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?
Feb 28, 2024 10:22
Johan Book
- Feb 29, 2024 8:03
I guess the size of the index relates to how many rows you have in OrderGroup_PurchaseOrder?
In our case we have 5 million orders. The size of the order note index is 26 GB. We even tried to drop it and create it anew. It's that size :(
In our case we have 5 million orders. The size of the order note index is 26 GB. We even tried to drop it and create it anew. It's that size :(
Quan Mai
- Feb 29, 2024 8:18
no it should not it. the clustered index size is actually the logical storage size of the table, so it should match the table size quite well (except if you another ten of other non clustered indexes)
Johan Book
- Feb 29, 2024 8:23
Well, that's the size of our index anyway... We've tried to drop it and recreate it, and it takes 20+ minutes and is 26 GB in size.
We only have the standard definition from Optimizely setup scripts, haven't changed anything on that table/index.
We only have the standard definition from Optimizely setup scripts, haven't changed anything on that table/index.
Quan Mai
- Feb 29, 2024 10:25
I would be interested in a copy of your database for a closer look, but that is probably impossible to do. anyhow, we have the Aha idea, let's see how that turn out
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.
Edited,
Feb 28, 2024 10:27
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.
Feb 28, 2024 10:34
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
Feb 28, 2024 11:35
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?