BAQ - Linking PORel with TranGlc

Epicor ERP 10
1

Have anyone tried linking PORel with TranGlc table to get GL information in E10.2.600? . In E9, same BAQ takes less than 20 seconds but in E10 it takes more than 20 minutes. Linking is PORel.Company = TranGlc.Company, PORel.PONum = TranGlc.Key1 , PORel.POLineNum = TranGlc.Key2 and PORel.PORelNum = TranGlc.Key3 with TranGlc.RelatedToFile = ‘PORel’. If I add TranGlc in mainquery, it times out. But If I add as a subquery, it returns in more than 20 minutes.

I tried adding a UD field in PORel to store “PORel” value so that I can use RelateToFile to link TranGlc but Epcior automatically changes the order and generates UD field to the last in the query. I tried storing SysRowId of TranGlc in PORel and even that didn’t help as it changes the order by putting SysRowId first and company second. Basically I couldn’t enforce the field order based on the index which is slowing down the query.

Any suggestions.

2

Try removing the PORel.Company = TranGLC.Company join condition. There are other queries where having the Company condition causes the query to time-out, while removing it lets the query run in seconds. If the BAQ is not cross-company and not external, there should be no problem with removing the Company condition.

We have a fairly complex BAQ that looks at expenses and POs to budget and takes upwards of 20 minutes to run depending on the timeframe. I have it in my queue to test removing the company criteria to see if it improves performance.

1 Like
3

Thanks a lot. Indeed it improved performance by miles. I have removed the company criteria from POHeader, PODetail, PORel and TranGlc and query execution is less than 2 seconds. Removing Company from related tables like Vendor, PurTerms and PurAgent added more time (7 times ). It’s more like a trial and error which is not a good sign.

2 Likes
4

Another thing epicor does they also join on the keys they don’t use Key4, Key5, Key6 (this is in PORel code) I wonder if itll improve it more.

row.Company == company_ex &&
         row.RelatedToFile == relatedToFile_ex &&
         row.Key1 == key1_ex &&
         row.Key2 == key2_ex &&
         row.Key3 == key3_ex &&
         row.Key4 == "" &&
         row.Key5 == "" &&
         row.Key6 == ""
5

Last week I had another performance issue with few BPMs on JobMtl Data Directive where I used Wizard Design Query condition to find JobHead linking ttJobMtl. When I changed from Wizard Design Query to Code is valid condition using C# code, Job Scheduling time reduced from 5 minutes to 20 seconds.

6

Its because the wizard joins ttJobMtl to JobHead and that is a big performance issue, epicor doesnt plan to address.

1 Like