Good morning everyone. I am once again asking for your technical support.
Over the last few months my users have started complaining about the slowness of the engineering workbench, specifically adding new parts. Nothing else has been affected so at first I thought it was just their perception, but I also started feeling it with much slower DMT changes to the BOMs, slowing down to a crawl. Upon investigation, there’s a couple methods that seem to be really slow. GetDatasetForTree goes from taking a few milliseconds to almost 10 seconds by the time all the parts have been added. And we only have about 30/40 parts in our BOMs, so I don’t think that’s something extraordinary. Checking it in at the end is also taking about 10 seconds.
Our BOMs have not changed much at all, number of parts is always about the same so there’s no reason why it started slowing down so much. My only ideas are that our product is made to order with not many repeats so we do have thousands of manufactured parts and BOMs, but that really shouldn’t interfere with speed of each individual one. The trace files are showing almost all that time is spent on the SQL processing, so that led me to look at the EcoMtl and PartMtl tables. We have a bit over 1 million rows on EcoMtl and a bit less than half that on PartMtl. Now I’m not 100% sure if those EcoMtl rows should be there, I had the idea that those should be cleared once the BOM is approved and checked in and transferred to the PartMtl table. We do create BOMs using some custom code so I’m wondering if there’s some cleanup that isn’t being done that is causing this.
Edit: We are on 10.2.400.18, in case that makes a difference.
Hopefully someone has some insight before I try to make a support request with Epicor. Not relishing having to pushing through the dismissals of it being our hardware being slow and finding someone technical that knows what’s happening
Are the users by chance using the same ECOGroup for everything? We had an ECOGroup for each engineer, and after a while, the ECOgroup gets loaded with information (even if there is nothing checked out) and will slow down. If you use a new one, it can be faster.
They use individual ECO Groups. For the automated BOM creation we recently changed it to also use ECO Groups tied to the users, so those were created recently, and they’re still very slow. I can double check with using a brand new one, but I believe it’s still the same.
So I created a new ECO Group, created a new revision and pasted in almost 50 parts from a different BOM. First instance of GetDatasetForTree ran in 276ms while the last one was over 6 seconds. Each new material coming in takes longer and longer.
ah, we used CADLink so we didn’t have to deal with bringing in many things into the BOM. We did some hardware using paste insert, but it seemed to be pretty consistent with a couple seconds per line. It may be a bug in epicor’s code where they are loading in more info than they need to. I’m not going to be any more help than that unfortunately.
If it’s custom could there be some variables causing a memory issue?
For something that is not totally related, but maybe something to think about…
We had a process that looped through the array each time to find the count. As the array got larger it took longer to process, who would have thought. We made a small adjustment to the code to count the items in the array more efficiently and the difference was immediately noticed. Not saying this is happening here, just saying that there may be something else going on that is causing the delay outside of the actual method call. Maybe some custom code on the method?
This to me points towards a missing or corrupted index in the database. First, make sure you have suitable indexes on all key fields in those tables (Company, PartNum, JobNum, MtlSeq, etc). If everything looks fine, you can use the following SQL query to rebuild all indexes in the database. Make sure you first take a backup of the database, and do this only outside normal hours, as rebuilding indexes might take a while, and database performance will suffer while it is going on.
Exec sp_msforeachtable 'ALTER INDEX ALL ON ? REBUILD'
Another thing to keep in mind is that for each new part you add, the dataset underneath grows in size, and that dataset is constantly being passed back and forth between the client and the server for each BO method call. If your network speed is slow, it will have a noticeable impact on the client responsiveness, more than you might expect. Make sure you are not testing this on a wifi connection or anything silly like that.
We had pretty much the same issue - this may be a bit too technical if you don’t have decent DBA skills or admin level access to your SQL DB, but we were able to find a query that was long-running whenever users were switching between, adding, etc. materials in Engineering workbench and then the SQL query optimizer suggested an index which we added and sped that process up immensely.
I actually submitted a ticket to Epicor and it’s gone up to Development which is supposed to be doing something, but it’s been quite a while with nothing to see yet. Hopefully we can actually figure it out.
We do have on prem sql and Epicor, and while I have messed around with sql enough I’ve never really done much index optimizing.
We are on 10.2.400.15 and slowness like this and it was related to attachments that are being verified as valid for the tree and a lot of them were not as server names had been changed. I used the attachment maintenance utility to change the paths that were incorrect.
