BAQ Speed Improvement TIP

Epicor ERP 10
5

The Way I Segregate Sub-Queries

Some Notes I gave to someone for replicating something like Chart Tracker.

  1. Get Transactional RAW Data with no meaning (ParTran, TranGLC etc…)

  2. Join on tables that give it more RAW Data (LaborDtl, JobProd…)

  3. Join on tables that give it human readable meaning (Customer, VendorPP, APInvHed, APInvDtl)

  4. Encapsulate via Sub-Queries, make it modular as much as possible for maintenance, addition and what data is necessary to query first and what can wait till end.

For example if I use my CTE and then wrap stuff up nicely into Sub-Queries… The Customer… I don’t need to Query until the end and only once () representing encapsulated sub-query

  • (( RAW Sub-Queries) + (More RAW Sub-Queries)) + (Customer + VendorPP + (APInvHed + APInvDtl))

I can get a recursion BAQ that crawls Job to Job, and builds a hierarchy to run in 9 seconds, on large set of rows that hit PartTran, TranGLC, JobProd, and 10+ other tables.

4 Likes