ok ok …
1 Like
I was not able to attend. Is there a recording or presentation summary. Thank you.
not to be Robin…
What about a TT to TT table? still bad.
I understand that you are creating an on Demand View which adds overhead.
TT to TT is fine they are small in memory POCOS have a blast!
Here are the slides @asmar there was a LOT more covered in conversation Q&A part unfortunately I don’t have that… Someone recorded it but they haven’t sent it to me yet.
E10Help Panel.pdf (1.3 MB)
1 Like
Also a few months ago - my exchange with Bart/Jose and Jose’s Screenshots of testing =)
Execution Time was 90 MS
Execution Time was 0 MS
“So it appears the assumption I made was correct, joining to the class object causes the “join” to occur in memory post SQL query so it would yield a much slower result… Good stuff!” - Jose
You need to always be aware that joining to an in memory structure is problematic. Sorry I did not review your question to dig it out.
I run into this in internal reviews as well. I still have not looked at your complete scenario but one of the things folks do a lot is push the ttrows (or some subset of columns) into the db as a ‘temp table’ and join against it. Very quick then even with millions of db records in partTran, etc type tables. The push of the data into the temp table and cleanup of records afterwards is many times faster than the ‘join to tt’
– Bart
3 Likes
Thanks for the insight Bart.
I went ahead and made some of the changes that you outlined and the code was still going quite slow. I think I am going to have to dive into this code and really break it down to figure out why it was created this way. In my opinion it is the code itself and we may have to think about using other methods to get the information as there has to be an easier/more efficient way that might not even require custom code.
The code was originally dictated by our credit department to our consultant at the time. It is meant to go through all the shiptos for the current customer and grab their total ordered to date , and give us the total outstanding balance for that current selected shipto. We are going to be talking with the credit department to hopefully clean up the naming convention as it does not accurately reflect what it is supposed to representing.
I don’t know if the code is populating all the custom fields in the Erp.ShipTo_UD table for every shipto for all customers at once somehow. We have as the same data fields used in dashboards that populate the information in seconds yet when pulling up a customer in customer maintenance it hangs sometimes up to 3 minutes in extreme cases.
It has definitely been fun trying to go into code written by other people trying to figure out everything they’ve created/touched. We have had a total of 3 different consulting companies doing things for us and my team has been brought into the fold with Epicor and we are now tasked with cleaning it all up as much as possible. I am going to have some further discussions with my team and see if there is something that we can come up with.
Also thanks for the pdf of the E10 Help Panel Jose! I attended it this year at insights and wanted to get those slides to have as a reference in addition to the notes I took.
1 Like
You should try this 
I usually do a prototype first in LINQPad and I can Debug and see results instantly… then with minor tweaks I turn it into a BPM.
3 Likes
Agree with @hkeric.wci on that prototyping approach. I would be curious how much data is in each query you are looking at. There is always the time someone has some outlier number of records that was not expected and throws this all off. Doing some breakdowns on how many records are in ttShipTo, then the first query, then the second, etc would be interesting to see.
You can also turn on the SQL and DB Perf traces in appserver.config to get some numbers on how long it takes to execute different items.
Thanks for that @hkeric.wci . I will look into LINQPad and use your example code posted above as a reference.
I will try prototyping it and see how it goes.
@Bart_Elia I’ll also turn on the SQL and DB traces and run through the process to get some numbers to analyze.
@IanCastellanos If you just download LINQPad (I would recommend the 99$ PAID) then you open those files in the above attachment, you should be ready to go with minimal configuration. You are welcome no worries, let us know if we can help.
@hkeric.wci I just bought myself a copy. Was on sale this month for $89.
I will play around with it and reply if I have any questions/issues.
Appreciate all the help from everyone in this thread!
Got LINQpad4 all set up and have run into what I think is a big problem. Seems that there is no ttShipto in our databases Which makes me wonder what the code is exactly doing unless I am missing something. Which could be the case since Epicor is still new to me.
ttShipTo is a virtual table that exists only during the transaction (tt = Temporary Table)
Okay that is what I was thinking based on what I know of how transactions work in Epicor. So I can’t technically use this tool to test the custom code in its current form it looks like.
I disabled the code in maintenance and am able to bring up the customers in seconds as opposed to minutes.
I am going to have to do some more digging and testing as this code is only going to cause the system to run slower as the number of ShipTos increase for customers as time goes on.
I just tried going through the code and I’m having a really hard time following what the intent is. This may be a case of re-write is best than a fix. Figure out what the end goal was and re-write it.
The code is looping through ttShiptos (there should be only one), then looping through Db.ShipTo that matches said ttShipTo (there should also be only one)
Do you know what the end goal is, seems messy at best?
This is where I am at with the code honestly. It might be best to take this back to the department and start from scratch to better define it. It was written by a third party who we ended have having to cut ties with due to issues with their development.
The end goal, to my knowledge, is to get the current amount that needs to be billed to the customer per shipto. When going into a customer as seen below, depending on the amount of shiptos it can take as quick as 2 seconds for new customers with little to no information but in the case of customers that we do many jobs for it can take up to a few minutes at times.
Once the customer is pulled up you can see their total bill to date. This code is supposed to grab that information and display it here. It is mainly used by our credit department to be able to have easy access to this information when looking at customers the only issue is that most of the users in our company use the customer maintenance page and it has been getting slower as time goes on due to their naturally being more and more jobs for each customer
And this is the small snippet of code that they are having me look at. There’s another one causing issues, also created for our credit department, that is over 1000 lines so that is going to be fun.
I tried… to make heads or tails of it… I THINK the following will do the same thing threw it together in notepad at the hotel lobby so apologize for the typos and misunderstandings.
foreach(var stt in ttShipTo)
{
decimal TotalBillToDate=0; TotalOrderToDate=0;
int custNum = stt.CustNum;
string shipToNum = stt.ShipToNum;
var shipToRecord = Db.ShipTo.Where(st=>st.CustNum == custNum && st.ShipToNum == shipToNum&& st.Company == callContextClient.CurrentCompany).FirstOrDefault();
TotalBillToDate = Db.InvcHead.Where(ih=>ih.Company == callContextClient.CurrentCompany && ih.CustNum == custNum && ih.ShipToNum == shipToNum).Select(id=>ih.DocInvoiceAmt).Sum();
TotalOrderToDate = Db.OrderHed.Where(oh => oh.Company == callContextClient.CurrentCompany && oh.ShipToCustNum == custNum && oh.ShipToNum ==shipToNum).Select(oh=>oh.DocTotalCharges+oh.DocTotalTax+oh.DocTotalMisc).Sum();
shipToRecord["TotalBillToDate_c"] = TotalBillToDate;
shipToRecord["TotalOrderedToDate_c"] = TotalOrderToDate - TotalBillToDate;
stt["TotalBillToDate_c"] =TotalBillToDate;
shipToRecord["TotalOrderedToDate_c"] =TotalOrderToDate - TotalBillToDate;
Db.Validate();
}
Ok I misunderstood when this was being executed. You will need to loop through the ttShipTo as before since its doing it for all ShipTos…
However this seems hiligh in-efficienct doing this every time you pull up a customer. Why not make a simple BAQ /Dashboard that shows this and bring that in using a BAQDataView or an Embeded Dashboard.
I’ve corrected the code above to use a loop… Still think you should go with a BAQview or Embeded Dashboard. though
I believe there is a dashboard that displays this data as well as other job information already created for the department but they are adamant that the information is available on this tab as well.
We were discussing what you propose internally as it causes so many issues and I suggested we should not be having to run this every single time someone goes into a customer as most don’t even need that information in the first place.
I am going to dive a little further into the dashboard they have already created and see what they have done to it.