BAQ Speed Improvement TIP

Epicor ERP 10
1

OK, true confessions… I just learned this today. I also reported it as a potential problem.

IF you have a BAQ that is slower then you desire, try breaking it up into Sub-Queries
Example, I had a query that linked the PART to PARTPLANT (so far not a problem), but then also linked PART to PRODGRUP… then I only wanted the PARTPLANT records where the plant equaled the ProductGroup.Plant… This caused the system to time out.

SO… to get around the timeout… I added a sub-query. In the sub query, I only put the Part, Plant, and the other two fields I wanted from Plant… I didn’t do any filtering in this sub query.
THEN I attached the sub-query into my top query, and joined the fields as before. Now, there are no time-outs.

I found that this also helped queries that link to data in the Customer and Shipto tables as well.

3 Likes
2

I make ample use of subqueries for this exact reason

3

Did you compare query plans, why is such difference?

4

Olga… I would LOVE to compare them, but as a field consultant I dont really have access to the SQL layer to check these out. I can send you the two BAQs however that compares them. The speed difference is very evident when there are over 10,000 records to be returned.

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.

3 Likes
6

Ditto as the others. I will also do a filter on RAW data that Haso mentioned above first so there are fewer records to join to add decoration like Part Description, Customer Name, Product Group Name, etc.