I was asked to look into the poor performance of quoting and ordering via our Magento implementation. I don’t know much about the integration to ECC or Magento, but as the new guy I started with what everyone else thought was the issue…SQL Server.
The use case is build the order / quote online. Typically 30 + lines. From what I see Magento waits for the user to finalize before submitting the data. To complete the insert of a row the Epicor API executes over 400 SQL statements. Many of these statements appear to be N+1 from Entity Framework lazy loading.
No one statement is excessively slow. Many executing in under 1ms. The longest being the actual insert statements. Total time to insert an order/quote line in 700 - 800ms. For over 400 SQL calls I would say not bad. However, add 30 lines and watch the code execute sequentially. The order/quote now takes 25 seconds to insert.
I tested this with the copy insert inside of the Epicor client and found the performance and process to be about the same. I have spoken with a few application developers. Their take was SQL performance was acceptable but thought the Entity code could likely be optimized. The gains would be minimal as these queries are all very fast but there are a lot of them.
My question to the group is this typical and if so what can we do? If not what else should I check?
Assuming your processing is done via standard E10 business logic for Order/Quote entry…
My guess is that Paste Insert/Update is giving you a good idea of “typical” times.
However, I’m wondering where the “poor performance” comes in?
e.g. a web interface/form where user(s) are waiting 25 seconds for a confirmation after submitting the order?
If so, maybe you can pull a Microsoft… hide processing times from the users?
e.g. Instead of providing feedback in real-time, leave the order in a pending state until after a confirmation email is sent to them?
Yes. Not just Microsoft, but Amazon, Etc, and every other large online retailer. In fact, even posts to Stack Overflow do the same because they tune for read performance and a brief delay in writing isn’t as critical. It is a very popular pattern.
25 seconds to create 30 lines? That seems pretty fast for epicor frankly.
I’m not sure how much control you have over the magento integration ('m going to get guess none)
But Epicor isn’t great for batch processing so maybe if you could create one line at a time as they go the total processing time would be hidden.
But again I doubt you have control over that. In terms of what to do I’d venture and say nothing much unfortunately if you don’t have access to modifying the integration which I think is out of the box for ECC
Yeah, MS is just who comes to mind whenever I think about masking processing times. I remember reading an article once about display tricks of Windows as it is starting… kind of interesting at the time, stuck with me.
The poor performance is in the user interface because Magento just spins for 25 seconds or longer depending on the order size. Users think it’s broken. Especially if the order is really large. Over 100 lines is not uncommon.
I feel others are right. We should mask the actually processing from the users. Submit in the background and let the user continue. If something fails or is wrong with the order we can send a notice.
When we coded for green screens (cough, cough am I that old???), the terminals were connected at 9.6 or 19.2 kbs and watching the screen paint was brutal. So we turned the screen off, allowed the IO to finish and then turn the screen back on. Poof! It looked instantaneous! It took the same amount of time but for the some reason, the users felt it went faster when they didn’t have to watch the display.
And you’re right about MS and Windows! That animation when Windows starts was one of those tricks. Same one our phones use today. That’s part of the reason the UI experts like progress bars since it sets the expectation of how long something might take.
Sounds like a Magento / Server Issue… Where are you hosting?
If I remember correctly Epicor < - > Magento isn’t real-time. Magento and Epicor Sync on a Schedule but the entire Magento UI runs purely on Magento and doesnt ping Epicor at all (unless they changed that).
I remember running Magento 9yrs ago and even then on a Dedicated Server it was slow. What are your server specs? If you are hosting with Epicor, I have seen their Servers be awful for Magento. Even the initial page load hosted by Epicor took too long.
Some data is synced via ECC. Customer accounts, parts among other data is sent to the Magento database on a schedule. However when quotes and orders are finalized they are posted to ECC which in turn accesses REST to write directly to SQL.
We host everything internally and have resources to spare. Magento has performance issues like you showed even when Epicor SQL is not involved. But those performance issues are outside of the scope of my testing.
