My recursive BOM BAQ is too slow for my needs. How do I speed this up

Epicor ERP 10
,
1

The BAQ I am working on goes from executing under a second per partnumber to taking over 12 seconds to execute per part number when I add this one table (MostRecentRev1 in * * and join criteria in bullet points below) into the recursion “union all” query.

If I don’t add this table I get the wrong results (doesn’t include all the child levels).

I tried making it a sub select but I keep getting errors or the children arent included. Does anyone know how I can speed this up and still get correct results?

I eventually need to add cost of child by qty needed per parent, identify class by child, and sum cost by class. Then compare that to Shipped Unit price to have a shipped parts cost of Sales BAQ by part shipped. I am clear on how to do that piece but this portion is so slow I cannot add that yet.

Anything with ‘Generic’ is a removed details just for public sharing.

with [ActiveParts] as 
(select 
	[Part].[Company] as [Part_Company],
	[Part].[PartNum] as [Part_PartNum],
	[Part].[ClassID] as [Part_ClassID],
	[Part].[PhantomBOM] as [Part_PhantomBOM]
from Erp.Part as Part
where (Part.InActive = FALSE  and Part.Company = 'Generic'))
 ,[PartRevLink] as 
(select 
	[ActiveParts].[Part_Company] as [Part_Company],
	[ActiveParts].[Part_PartNum] as [Part_PartNum],
	[PartRev].[RevisionNum] as [PartRev_RevisionNum],
	((Row_number() over (partition by ActiveParts.Part_Company, ActiveParts.Part_PartNum, PartRev.Plant order by PartRev.Approved desc, PartRev.EffectiveDate desc))) as [Calculated_RevRowNum],
	[PartRev].[Plant] as [PartRev_Plant]
from  ActiveParts  as ActiveParts
left outer join Erp.PartRev as PartRev on 
	ActiveParts.Part_Company = PartRev.Company
	and ActiveParts.Part_PartNum = PartRev.PartNum
	and ( PartRev.Plant = 'Generic'  )

where (ActiveParts.Part_Company = 'Generic'))
 ,[MostRecentRev] as 
(select 
	[PartRevLink].[Part_Company] as [Part_Company],
	[PartRevLink].[Part_PartNum] as [Part_PartNum],
	[PartRevLink].[PartRev_RevisionNum] as [PartRev_RevisionNum],
	[PartRevLink].[PartRev_Plant] as [PartRev_Plant]
from  PartRevLink  as PartRevLink
where (PartRevLink.Calculated_RevRowNum = 1))
 ,[Anchor] as 
(select 
	[MostRecentRev].[Part_Company] as [Part_Company],
	[MostRecentRev].[Part_PartNum] as [Part_PartNum],
	[MostRecentRev].[PartRev_RevisionNum] as [PartRev_RevisionNum],
	(0) as [Calculated_Level],
	(Convert(decimal (18,8),1)) as [Calculated_QtyPer],
	(MostRecentRev.Part_PartNum) as [Calculated_RootPart],
	(MostRecentRev.Part_PartNum) as [Calculated_ParentPart],
	(Convert(decimal (18,8),1)) as [Calculated_TotalQtyPer]
from  MostRecentRev  as MostRecentRev
where (MostRecentRev.Part_PartNum in ('Generic1', 'Generic2'))
union all
select 
	[PartMtl].[Company] as [PartMtl_Company],
	[PartMtl].[MtlPartNum] as [PartMtl_MtlPartNum],
	[MostRecentRev1].[PartRev_RevisionNum] as [PartRev_RevisionNum],
	(Anchor.Calculated_Level +1) as [Calculated_BomLevel],
	[PartMtl].[QtyPer] as [PartMtl_QtyPer],
	[Anchor].[Calculated_RootPart] as [Calculated_RootPart],
	(PartMtl.PartNum) as [Calculated_ParentPartNum],
	(Convert(decimal (18,8),(PartMtl.QtyPer* Anchor.Calculated_TotalQtyPer))) as [Calculated_TotalQtyPer]
from  Anchor  as Anchor
inner join Erp.PartMtl as PartMtl on 
	Anchor.Part_Company = PartMtl.Company
	and Anchor.Part_PartNum = PartMtl.PartNum
	and Anchor.PartRev_RevisionNum = PartMtl.RevisionNum
	and ( PartMtl.Company = 'Generic'  )

*inner join  MostRecentRev  as MostRecentRev1 on *
*	PartMtl.Company = MostRecentRev1.Part_Company*
*	and PartMtl.MtlPartNum = MostRecentRev1.Part_PartNum)*

select 
	[Anchor1].[Part_Company] as [Part_Company],
	[Anchor1].[Part_PartNum] as [Part_PartNum],
	[Anchor1].[PartRev_RevisionNum] as [PartRev_RevisionNum],
	[Anchor1].[Calculated_Level] as [Calculated_Level],
	[Anchor1].[Calculated_QtyPer] as [Calculated_QtyPer],
	[Anchor1].[Calculated_RootPart] as [Calculated_RootPart],
	[Anchor1].[Calculated_ParentPart] as [Calculated_ParentPart],
	[Anchor1].[Calculated_TotalQtyPer] as [Calculated_TotalQtyPer]
from  Anchor  as Anchor1
2

I had to create a BOM BAQ for a project at work and ended up creating it as an External BAQ which executed much faster than a BAQ. :no_good_man:

3

Do you know if the External BAQs load into the RestAPI like the regular BAQs. They will need the BAQ for both a dashboard and for FP&A down the road. I know FP&A loads in BAQs from RestAPI. I may be limited on my knowledge of this subject so if you have a workaround I am very open to that as well.

4

The External BAQs I’ve created are available in Swagger so I would think that you can use them via Rest. I think you can give it a try.

5

FYI, normal BAQ’s do checks on each join to verify the user calling it has permission to view the data in the displayed columns. If you have a lot of joins and subqueries (as recursions tend to do) the system makes this check repeatedly. Which can result in giant overhead costs even when returning a handful of rows.

External BAQ’s skip all of this and run basically straight SQL queries, which is why it’s quicker, but also means you’re bypassing field security just as if you were querying DB from SSMS or SSRS or whatever other tool you have. It’s perfectly fine so long as you’re mindful of that.

You can use EBAQ’s in updateable dashboards (which use normal UpdateExt methods) as well as REST endpoints.

3 Likes