Allow for significant order inventory processing (50 line items+)

[havenswift-hosting]

Not looked in detail what is causing the issue but changing the order status from Pending to processing on orders takes a HUGE amount of processing time such that orders with large numbers of inventory lines will often cause a fatal error due to exceeding the allowed processing time.

This has previously affected clients that enter orders against a "store" customer in order to update stock levels and keep track of total sales where product has been sold at a show over a couple of days - the "solution" has been to put these in as multiple smaller orders. However, now have clients taking orders with large number of line items which cant then be processed. For example one client took an order with 500 line items this morning

[bhsmither]

CC642? CC643? Do you think it makes a difference?

[havenswift-hosting]

No difference at all - this has been happening for a LONG time

[hairy-dog]

Not looked at this code, but for a different platform, a function for creating address labels seemed very slow. When I looked, it wasn't selecting just the orders with the correct status at the beginning of the query. Fixing that took a few seconds and made aassive difference.

[havenswift-hosting]

100% it will be the query that needs optimising - just havent had time to look in detail and do the analysis

[abrookbanks]

I haven't seen any stores suffering from this. I could be a number of things.

Please post back when you have more info..

[havenswift-hosting]

Never see any problems with normal orders with maybe up to 20 or 30 product line items - try it with an order with 100 or even 500 plus line items as in this case and easily reproducible - tested on multiple stores. I will try and do some analysis of the queries while it is running but it is 100% a problem

[abrookbanks]

Christ. How many line items!?! Is this Asda.com? 😂

[havenswift-hosting]

Yep in this case over 500 and this was a single customer order - unbelievable if I hadn’t seen it for myself ! However as I said this is a common issue experienced by multiple customers putting in aggregated orders say from a weekend fair / market. Don’t know what the number of line items that triggers it but way, way less than the 500 here - maybe 60 plus ?

Guessing the logic is wrong - maybe loading all line items in one go rather than processing line by line where it obviously wouldn’t make any difference how many line items there were.

[bhsmither]

Just to be clear, this issue is describing a problem that occurs specifically when the admin moves a complete and final order from Pending to Processing? And is aggravated by having a sufficient number of inventory items?

Is it important that the settings say to adjust inventory when the order moves to Processing?

And also to be clear, these cases where this has been observed, they do not have any plugins/snippets installed that execute in the context of moving an order from Pending to Processing?

[bhsmither]

My initial, very shallow analysis of moving an order from Pending to Processing (but having only one inventory item) shows the greatest time expenditures are when executing the code near when, and probably including the time to execute the query itself:

UPDATE CubeCart_order_inventory (11 milliseconds per item)
UPDATE CubeCart_order_summary (13 milliseconds once with basic data)
Emailing Cart: Payment Received and INSERT into CubeCart_email_log (2.7 seconds)
Emailing Cart: Order Confirmation and INSERT into CubeCart_email_log (1.8 seconds)
Emailing Admin: Order Received and INSERT into CubeCart_email_log (2.0 seconds)

Overall, this took 7 seconds to complete.

My first guess is that Smarty takes a lot of time rendering the email template and that the time scales linearly based on the number of items.

I do not yet have time hacks for Smarty's time expenditure, nor for PHPMailer's time expenditure.

[abrookbanks]

What method are you using for sending the email? SMTP will probably take longer due to the handshaking.

[havenswift-hosting]

My first guess is that Smarty takes a lot of time rendering the email template and that the time scales linearly based on the number of items.

What method are you using for sending the email? SMTP will probably take longer due to the handshaking.

The rendering of the email templates does take a while but is pretty irrelevant to this example - it might be relevant if changing 100 orders from Pending to Processing but this is a single order but with a LOT of line items and therefore only one set of the three emails will be sent.

In the example with the 500 plus line items, stock levels are adjusted when the order is placed and therefore even that is not relevant when changing to Processing.

[abrookbanks]

CubeCart wasn't really designed or tested under this niche situation. It's quite a big ask really.

Listing 500 products to a page is a big job for any scripts and that's not such an unusual request as this.

[havenswift-hosting]

CubeCart wasn't really designed or tested under this niche situation. It's quite a big ask really.

It isnt really a niche situation - I know at least 4 of our customers that this effects - 3 of them can get around it as it only happens when they enter large cumulative orders as I said before perhaps taken at fairs or shows over a weekend. However, this one was an online order from a customer

Listing 500 products to a page is a big job for any scripts and that's not such an unusual request as this.

Surely, whatever process is being done at that point should simply loop through the product detail lines and deal with each one in turn - even 500 MySQL updates one after the other should only take a couple of seconds at most

[bhsmither]

In the example with the 500 plus line items, stock levels are adjusted when the order is placed and therefore even that is not relevant when changing to Processing.

So you are confirming that the admin settings are such that inventory is decremented when the order goes to Pending.

Have you confirmed that there is not any plugin or snippet that gets executed in the context of moving an order to Processing?

[abrookbanks]

Sorry for the change of status here but I still don't see this as a bug. It's overloaded really. I have no doubt optimizations could be made. 👍🏻 This has to be low priority over other issues like Elastic Search and new skin.

[havenswift-hosting]

So you are confirming that the admin settings are such that inventory is decremented when the order goes to Pending.

On this one site that is the case but it makes no difference - other sites decrement stock at Processing and have the same problem

Have you confirmed that there is not any plugin or snippet that gets executed in the context of moving an order to Processing?

On this site no snippets installed and nothing other than basic standard plugins. Again, this is easily reproduceable on multiple sites all with different plugins etc

Sorry for the change of status here but I still don't see this as a bug. It's overloaded really.

Sorry, I would disagree - store allows an order to be placed and then cannot process it at all and falls over with a timeout error - that is a bug

This has to be low priority over other issues like Elastic Search and new skin

I would have to agree that ES is highest priority and this doesnt affect that many sites (where there isnt a workaround anyway) but would still content that it is a bug and not a feature request.

[abrookbanks]

The old bug/feature debate. 😂

[bhsmither]

I performed the same experiment as before, but (aided by my browser's Developer's Tools) loaded the web form with 100 added inventory line items. Processing the POSTed form took a total of ten seconds where seven seconds of that is rendering and sending emails. (Note: a key factor in this experiment - with unknown consequences - is that all 100 added inventory line items are the same item.)

Have we eliminated the server environment as a possible cause? Might there be a sanity throttle on hitting the database?

[DiMarca-Online]

Being able to put an order that is paid for from 'pending' to 'processing' and once it's sent, to 'complete' (with a tracking code), is not a feature. That is a basic requirement to a shopping system, however big the order is.

Right now I have this big order still pending, because I'm unable to change the status.

I hope you will reconsider fixing this bug. Thank you.

[abrookbanks]

500 line items to an order is a huge ask on the software and incredibly niche.

[DiMarca-Online]

Maybe so, but obviously there are also issues with orders of 50+ items.

Is there anyone that could help me find another way to put that order on 'complete'? Maybe you smart guys have some tricks up your sleeve? ;)

[abrookbanks]

A big competitor of ours has a max limit of 100 line items, I expect for this very reason. Out of interest what is the largest amount of line items that has worked?

[abrookbanks]

I would have thought that we need to impose a maximum line item limit to prevent this problem. The customer can then then pass through multiple orders.

[havenswift-hosting]

Al

500 line items to an order is a huge ask on the software and incredibly niche.

Why exactly is it a huge ask - what part of the code is causing this when switching from Pending to Processing or even directly to Completed (when you can discount stock control as that was done when creating the order)

and incredibly niche.

500 may be niche but this happens at values WAY below that - I dont think it can even do anywhere close to 100 but I dont have definitive figures.

I would have thought that we need to impose a maximum line item limit to prevent this problem. The customer can then then pass through multiple orders.

That is 100% needed - it cannot be left where an order can be entered but then not progressed. I would hope that some optimisation can be done to increase the number of line items but then a clear limit imposed

[DiMarca-Online]

Maybe ask Ian about the other cases he had, because this was my first problem.

I just counted another order of the same lady, as she always does big orders (not big like the 500 lines, but still) and that one was 184 lines. This was before the upgrade though, does that have anything to do with it?

I personally would hate a maximum line item as that would negatively affect my sales.... a maximum of line items will influence their shipping costs.

[havenswift-hosting]

a maximum of line items will influence their shipping costs.

That is also a very big consideration

[DiMarca-Online]

Why exactly is it a huge ask - what part of the code is causing this when switching from Pending to Processing or even directly to Completed (when you can discount stock control as that was done when creating the order)

I also wonder about that as I would expect the system to have more trouble with creating the order in the first place (to discount the stock) then to put it on processing or complete.

[DiMarca-Online]

And the stock was discounted perfectly with the 500 line order ;)

[abrookbanks]

The final word for now is simply that CubeCart has not been designed or tested to cope with huge orders. This isn't unreasonable.

[abrookbanks]

.. hence the feature request tag.

[abrookbanks]

Magento have a note about this: https://support.magento.com/hc/en-us/articles/360048550332-Best-practice-for-number-of-products-in-cart-in-Magento

Above 500 products in a cart the cart and checkout flows are not guaranteed to work.

@havenswift-hosting if your support team can locate any bottlenecks please feed it back to the community. I do not deny and have no doubt that optimisations could be made.

[abrookbanks]

I believe Shopify has a 100 line item limit. All software has limits which is generally bound by the resource limits imposed by its environment (hosting).

[tonyrsutton]

If Magento even have a knowledge base on this and they're the biggest shopping cart software out there, then we know this is a huge impact. Many of them do have a limit. Nothing is unlimited.

Therefore I have to agree with @abrookbanks

[abrookbanks]

Thank you @tonyrsutton

@havenswift-hosting I do appreciate how you are a little biased to appease your client. I can't really blame you for that. 😀

[havenswift-hosting]

I do appreciate how you are a little biased to appease your client. I can't blame you for that and want to see this "resolved".

Actually for the three clients that have experienced this so not so niche. Yes of course I am pushing to get something done because several clients have experienced these issues - that doesnt mean to say that I am not right though :)

It would be good to at least have a bit of an investigation into the cause even if it turns out to be too big a job to fix - at the moment it seems to be being dismissed simply as not possible and niche

If Magento even have a knowledge base on this and they're the biggest shopping cart software out there, then we know this is a huge impact. Many of them do have a limit. Nothing is unlimited. Therefore I have to agree with @abrookbanks

Well thank you for your opinion but to directly compare different shopping carts without having any understanding of what is actually causing the problem (which is likely to be different in each case) is naive. A limit would be almost acceptable if it was documented and enforced so customers cannot create an order larger than it.

[hairy-dog]

It seems to me that two arguments are being conflated here. The point about Magento having a KB entry about a similar issue simply tells us that it is an issue when it occurs. This is unquestionable. If CC has a limit to the number of items in an order, it would be good to know what this limit is. Then clients could work round it, or choose a different platform. However, finding out what is causing the limitation is an entirely different kettle of worms. If the cause can be found, perhaps it can be fixed.

[abrookbanks]

I'm sure you can imagine that I've dealt with hundreds and hundred of stores over the years and I've never once seen this from recollection. I'm not saying I don't believe you but to have three is impressive. You attract the niche crowd Ian. 😀

[tonyrsutton]

Well thank you for your opinion but to directly compare different shopping carts without having any understanding of what is actually causing the problem (which is likely to be different in each case) is naive. A limit would be almost acceptable if it was documented and enforced so customers cannot create an order larger than it.

At the end of the day, Magento is huge and is owned by Adobe with loads of developers working on this. If they did think this is a bug, they would have resolved it by now. But they haven't and put a limit on it. Therefore it could be a limitation of PHP, SQL and/or web hosting hardware all combined. One or more of them might have a bottleneck which causes this.

If it's a bottleneck from PHP or SQL, then it's out of scope of this. It might be worth checking with them and see if they could improve their coding to resolve the bottleneck?

[abrookbanks]

@hairy-dog the limit is resources not set by CubeCart. At the same time there is no doubt at all that the CubeCart code could probably be fairly significantly optimised.

[hairy-dog]

That's part of the point, isn't it? Something in the environment is setting the limit. Ideally, CC would identify this something and suggest (or impose) a limit that kept acceptable performance.

[DiMarca-Online]

Magento: Above 500 products in a cart the cart and checkout flows are not guaranteed to work.

So then my question is: is that the maximum for CubeCart as well? If so, even though I'm not happy about setting a limit (for the reasons explained above), I can live with with a limit of 500 items. If that's imposed and noone can put more than 500 items in their cart, I can communicate this with my customers.

For now I'm still hoping you can put my 500+ order on complete for me, Al?

[abrookbanks]

So then my question is: is that the maximum for CubeCart as well?

The only limits are the resources defined by your hosting provider. CubeCart doesn't currently set limits for the amount of line items but there are off the back of this strong reasons to start. It's not been approached yet but it could be possible for darker issues such as denial of service or some kind of overflow.

For now I'm still hoping you can put my 500+ order on complete for me, Al?

If you were a technical support customer or hosting customer of ours I would of course look into this as a case issue but not promise a solution. I still do not consider this to be a bug.

[abrookbanks]

@hairy-dog

That's part of the point, isn't it? Something in the environment is setting the limit. Ideally, CC would identify this something and suggest (or impose) a limit that kept acceptable performance.

I think this would be quite challenging to calculate.

[tonyrsutton]

If you put the latest version of CubeCart (with NO modifications) on:

1) Shared hosting 2) Virtual hosting 3) Dedicated server

Which one will perform the best?

That's the bottleneck. So I am still going with 'This is not a bug'. But I still believe in TWEAKING the server including the additional software (i.e. PHP, SQL, Apache, etc) that is used to run CubeCart - you can tweak them as much as you can but ultimately, there WILL always be a limit on one of them.

[abrookbanks]

Unfortunately that's a "string" question. You can have a shared hosting account that's significantly more powerful than a dedicated server. At the same time depending on settings and available resources all three could be tweaked to be more powerful or possibly even less powerful and more patient.

[abrookbanks]

@hairy-dog it's a tricky task to calculate the required resources. I see where you are coming from though and it's a great shout.

[bhsmither]

Knowing now how to have the admin make an order with 100 items (of the same item) easily, I can make the admin create an order with 1000 items just as easily.

But then I am going to hit PHP's 'max_input_vars' of 1000. So, I will first need to adjust the server environment.

Can the affected installations please check their PHP-Info settings and state what the 'max_input_vars' is set at? As well as any limits imposed by the web server.

[havenswift-hosting]

For now I'm still hoping you can put my 500+ order on complete for me, Al?

@DiMarca-Online dont worry about that - this is simple to do and already done - dont need more support from anyone else.

[abrookbanks]

@havenswift-hosting be sure to contribute back to the community which resources were increased and by how much to get it pushed through.

It would be good to push this through PHP X-Ray or cachegrind.