# BAQ - Linking PORel with TranGlc **URL:** \ **Category:** Epicor ERP 10\ **Tags:** baq\ **Created:** [September 18, 2020, 10:37am UTC](https://www.epiusers.help/t/baq-linking-porel-with-tranglc/71055 "2020-09-18T10:37:33Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![Arul](https://www.epiusers.help/letter_avatar_proxy/v4/letter/a/b2d939/32.png) [@Arul](https://www.epiusers.help/u/Arul)\ **Post date:** [September 18, 2020, 10:37am UTC](https://www.epiusers.help/t/baq-linking-porel-with-tranglc/71055/1 "2020-09-18T10:37:33Z")
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. ---
**Author:** ![tsmith](https://www.epiusers.help/user_avatar/www.epiusers.help/tsmith/32/64774_2.png) [@tsmith](https://www.epiusers.help/u/tsmith)\ **Post date:** [September 18, 2020, 12:37pm UTC](https://www.epiusers.help/t/baq-linking-porel-with-tranglc/71055/2 "2020-09-18T12:37:53Z")
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. > [@Improving BAQ Performance](https://www.epiusers.help/t/improving-baq-performance/54387/31): > > The BAQ performance inconsistencies with adding or not adding the “Company = CurrComp” criteria, have been resolved with work done in 10.2.700. Prior to that work, the BAQ engine would automatically use a Temp Table it populated with valid Company values for the query and the TT would be “joined” to the Company column. Depending on what other criteria was in play, that join could result in sub-optimal performance. The BAQ has to account for a number of different scenarios including Cross Compan… 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. ---
**Author:** ![Arul](https://www.epiusers.help/letter_avatar_proxy/v4/letter/a/b2d939/32.png) [@Arul](https://www.epiusers.help/u/Arul)\ **Post date:** [September 18, 2020, 2:27pm UTC](https://www.epiusers.help/t/baq-linking-porel-with-tranglc/71055/3 "2020-09-18T14:27:59Z")
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. ---
**Author:** ![hkeric.wci](https://www.epiusers.help/user_avatar/www.epiusers.help/hkeric.wci/32/52019_2.png) [@hkeric.wci](https://www.epiusers.help/u/hkeric.wci)\ **Post date:** [September 18, 2020, 3:01pm UTC](https://www.epiusers.help/t/baq-linking-porel-with-tranglc/71055/4 "2020-09-18T15:01:48Z")
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. ```auto 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 == "" ``` ---
**Author:** ![Arul](https://www.epiusers.help/letter_avatar_proxy/v4/letter/a/b2d939/32.png) [@Arul](https://www.epiusers.help/u/Arul)\ **Post date:** [September 20, 2020, 11:27pm UTC](https://www.epiusers.help/t/baq-linking-porel-with-tranglc/71055/5 "2020-09-20T23:27:12Z")
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. ---
**Author:** ![hkeric.wci](https://www.epiusers.help/user_avatar/www.epiusers.help/hkeric.wci/32/52019_2.png) [@hkeric.wci](https://www.epiusers.help/u/hkeric.wci)\ **Post date:** [September 21, 2020, 12:38pm UTC](https://www.epiusers.help/t/baq-linking-porel-with-tranglc/71055/6 "2020-09-21T12:38:03Z")
Its because the wizard joins ttJobMtl to JobHead and that is a big performance issue, epicor doesnt plan to address. > [@Joining "TT" records to regular DB records in BPMs](https://www.epiusers.help/t/joining-tt-records-to-regular-db-records-in-bpms/45646/21): > > I wonder if Epicor will ever change their underlying generated code. I still see many folks join on ttTable using the WIdget. > [@E10 BPM Slow due to Join?](https://www.epiusers.help/t/e10-bpm-slow-due-to-join/45238/13): > > 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 [3] “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 quest…