BAQ TEST Performance

[Andrewpech (Andy Pech)]

Good Day All

I’m trying to build a “FULFILMENT DASHBOARD” of a sort. Modeled after the fulfillment WB, I have a pretty ratty looking build that I have to clean up for a concept.
I was initially testing it filtered to one order. But now that I opened it up, it will not TEST and just sits “EXECUTING”

I rebuilt the query of a sort inside SQL (minus some minor top level calculations that were failing) and it returns 16K rows in about 16 seconds and the BAQ test is still executing for 30 minutes now (I doubt it’ll run)

BAQ have some limitations that I don’t know about? Or are there any suggestions to get it to run?

[cfinley (Chance Finley)]

Sometimes the mirroring of queries from BAQ to SQL can reveal how you should have built the BAQ. I’ve done this myself and realized how i built the BAQ was wrong/suboptimal.

Could you provide your recreated SQL query so I can see what you were trying to accomplish? Also have you tried seeing if deleting a specific table drastically increases performance? Perhaps you have a bad link that has slipped by. Its also worth mentioning the below link with some BAQ Engine issues that plague anything prior to 10.2.700 and below

Improving BAQ Performance

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…

RDD BAQ Report timeout

This suggestion ultimately solved the problem of the long Query runtime. BAQ with date range of 06/01/2020 - 06/30/2020. 25 records that represent all the days we shipped something. With 8 inline subquery calculated fields. Run without any company criteria. Non Cross Company BAQ 13.683 minutes Run with company hardcoded company = ‘abcdef’ on all tables marked with 1 including inline subqueries 3.45 minutes Run with BAQ Special Constant of CurComp on all tables marked with a 1 including inl…

P.S. I hope you are not still on 10.0 which is listed on your profile. I would not expect great results from that buggy version. Screenshot looks kinetic-ish though. So I think you are okay on that front

[Andrewpech (Andy Pech)]

oops… I guess i need to update my profile… Were on 10.2.600

Thanks for the post links. I’ll review those and see what I have going on. I was going to start removing some subqueries and working back from there today.

This is the query that I dumped into SMS

/*
 * Disclaimer!!!
 * This is not a real query being executed, but a simplified version for general vision.
 * Executing it with any other tool may produce a different result.
 */
 
select distinct
	[PartAlloc].[PickingQty] as [PartAlloc_PickingQty],
	[PartAlloc].[PickedQty] as [PartAlloc_PickedQty],
	[OrderHed].[OrderNum] as [OrderHed_OrderNum],
	[OrderRel].[OrderLine] as [OrderRel_OrderLine],
	[OrderRel].[OrderRelNum] as [OrderRel_OrderRelNum],
	[OrderRel].[ReqDate] as [OrderRel_ReqDate],
	(OrderDtl.UnitPrice * OrderDtl.OrderQty) as [Calculated_ExtPrice],
	(RemainingToShip * OrderDtl.UnitPrice) as [Calculated_RevExtPrice],
	[OrderTotals].[Calculated_OrderValue] as [Calculated_OrderValue],
	[OrderTotals].[Calculated_RevOrderValue] as [Calculated_RevOrderValue],
	[PartPlant].[SourceType] as [PartPlant_SourceType],
	[OrderDtl].[PartNum] as [OrderDtl_PartNum],
	[OrderDtl].[LineDesc] as [OrderDtl_LineDesc],
	(OrderRel.OurReqQty- ShippedQty) as [Calculated_RemainingToShip],
	[PartDtl_NxtJobPO].[Calculated_PO_JOB] as [Calculated_PO_JOB],
	[PartDtl_NxtJobPO].[PartDtl_NextJobPO_Quantity] as [PartDtl_NextJobPO_Quantity],
	[PartDtl_NxtJobPO].[Calculated_Confirm_Released] as [Calculated_Confirm_Released],
	(JobASM.Calculated_EST_Hours- JobASM.Calculated_ACT_Hours) as [Calculated_JobHrsRemain],
	[OrderRel].[FWBMemoText_c] as [OrderRel_FWBMemoText_c],
	[OrderDtl].[ProdCode] as [OrderDtl_ProdCode],
	[OrderHed].[ShipOrderComplete] as [OrderHed_ShipOrderComplete],
	[OrderHed].[OrderHeld] as [OrderHed_OrderHeld],
	[Customer].[CreditHold] as [Customer_CreditHold],
	(case 
     when OrderHed.OrderHeld = 1 then 1 
     when Customer.CreditHold = 1 then 1
     else 0
 end) as [Calculated_OnHold],
	[OrderDtl].[UrgentFlag_c] as [OrderDtl_UrgentFlag_c],
	[Customer].[Name] as [Customer_Name],
	(datepart(month, OrderRel.ReqDate )) as [Calculated_ReqMonth],
	(DATEPART(wk,OrderRel.ReqDate)) as [Calculated_ReqWeek],
	(datepart(year, OrderRel.ReqDate)) as [Calculated_ReqYear],
	(case 
     when Constants.Today > OrderRel.ReqDate then 'Past Due'
     else 'Future' 
 end) as [Calculated_FutureStatus],
	[OrderDtl].[UnitPrice] as [OrderDtl_UnitPrice],
	[Currency].[CurrencyCode] as [Currency_CurrencyCode],
	[OrderRel].[OurReqQty] as [OrderRel_OurReqQty],
	[OrderRel].[SalesUM] as [OrderRel_SalesUM],
	(OrderRel.SellingJobShippedQty + OrderRel.SellingStockShippedQty) as [Calculated_ShippedQty],
	(ISNULL(SubQuery2.Calculated_OnHandQty,0)) as [Calculated_QtyOnHand],
	[OrderRel].[IUM] as [OrderRel_IUM],
	[OrderRel].[RevisionNum] as [OrderRel_RevisionNum]
from Erp.OrderHed as OrderHed
inner join Erp.OrderDtl as OrderDtl on 
	OrderHed.Company = OrderDtl.Company
	and OrderHed.OrderNum = OrderDtl.OrderNum
	and ( OrderDtl.OpenLine = 1  and OrderDtl.ProdCode = 'PART'  )

inner join Erp.OrderRel as OrderRel on 
	OrderDtl.Company = OrderRel.Company
	and OrderDtl.OrderNum = OrderRel.OrderNum
	and OrderDtl.OrderLine = OrderRel.OrderLine
	and ( OrderRel.OpenRelease = 1  )

left outer join Erp.PartAlloc as PartAlloc on 
	PartAlloc.Company = OrderRel.Company
	and PartAlloc.OrderNum = OrderRel.OrderNum
	and PartAlloc.OrderLine = OrderRel.OrderLine
	and PartAlloc.OrderRelNum = OrderRel.OrderRelNum
left outer join  (select 
	[PartWhse].[Company] as [PartWhse_Company],
	[PartWhse].[PartNum] as [PartWhse_PartNum],
	(SUM(PartWhse.OnHandQty)) as [Calculated_OnHandQty]
from Erp.PartWhse as PartWhse
group by PartWhse.Company,
	PartWhse.PartNum)  as SubQuery2 on 
	OrderRel.Company = SubQuery2.PartWhse_Company
	and OrderRel.PartNum = SubQuery2.PartWhse_PartNum
left outer join  (select 
	[JobProd].[Company] as [JobProd_Company],
	[JobProd].[OrderNum] as [JobProd_OrderNum],
	[JobProd].[OrderLine] as [JobProd_OrderLine],
	[JobProd].[OrderRelNum] as [JobProd_OrderRelNum],
	(case
 when JobHead.QtyCompleted > 0 AND PartTran.TranType = 'MFG-STK' or JobHead.QtyCompleted > 0 AND PartTran.TranType = ''
 then 0 
 else JobHead.QtyCompleted
 end) as [Calculated_JobQty]
from Erp.JobHead as JobHead
inner join Erp.JobProd as JobProd on 
	JobHead.Company = JobProd.Company
	and JobHead.JobNum = JobProd.JobNum
	and ( JobProd.OrderNum <> 0  )

inner join Erp.PartTran as PartTran on 
	JobHead.Company = PartTran.Company
	and JobHead.JobNum = PartTran.JobNum
where (JobHead.JobEngineered = true  and JobHead.JobClosed = false  and JobHead.JobComplete = false  and JobHead.QtyCompleted > 0)
 and ((case
 when JobHead.QtyCompleted > 0 AND PartTran.TranType = 'MFG-STK' or JobHead.QtyCompleted > 0 AND PartTran.TranType = ''
 then 0 
 else JobHead.QtyCompleted
 end) > 0))  as SubQuery3 on 
	OrderRel.Company = SubQuery3.JobProd_Company
	and OrderRel.OrderNum = SubQuery3.JobProd_OrderNum
	and OrderRel.OrderLine = SubQuery3.JobProd_OrderLine
	and OrderRel.OrderRelNum = SubQuery3.JobProd_OrderRelNum
inner join  (select 
	[OrderHed_Totals].[Company] as [OrderHed_Totals_Company],
	[OrderHed_Totals].[OrderNum] as [OrderHed_Totals_OrderNum],
	(sum(OrderDtl_Totals.UnitPrice* OrderRel_Totals.OurReqQty)) as [Calculated_OrderValue],
	(SUM(OrderDtl_Totals.UnitPrice * (OrderRel_Totals.OurReqQty-(OrderRel_Totals.OurJobShippedQty + OrderRel_Totals.OurStockShippedQty)))) as [Calculated_RevOrderValue]
from Erp.OrderHed as OrderHed_Totals
inner join Erp.OrderDtl as OrderDtl_Totals on 
	OrderHed_Totals.Company = OrderDtl_Totals.Company
	and OrderHed_Totals.OrderNum = OrderDtl_Totals.OrderNum
inner join Erp.OrderRel as OrderRel_Totals on 
	OrderDtl_Totals.Company = OrderRel_Totals.Company
	and OrderDtl_Totals.OrderNum = OrderRel_Totals.OrderNum
	and OrderDtl_Totals.OrderLine = OrderRel_Totals.OrderLine
group by [OrderHed_Totals].[Company],
	[OrderHed_Totals].[OrderNum])  as OrderTotals on 
	OrderRel.Company = OrderTotals.OrderHed_Totals_Company
	and OrderRel.OrderNum = OrderTotals.OrderHed_Totals_OrderNum
left outer join Erp.PartPlant as PartPlant on 
	OrderRel.Company = PartPlant.Company
	and OrderRel.Plant = PartPlant.Plant
	and OrderRel.PartNum = PartPlant.PartNum
left outer join  (select 
	(ROW_NUMBER() OVER (PARTITION BY PartDtl_NextJobPO.PartNum ORDER BY  PartDtl_NextJobPO.DueDate)) as [Calculated_LineNum],
	[PartDtl_NextJobPO].[Company] as [PartDtl_NextJobPO_Company],
	[PartDtl_NextJobPO].[PartNum] as [PartDtl_NextJobPO_PartNum],
	[PartDtl_NextJobPO].[JobNum] as [PartDtl_NextJobPO_JobNum],
	[PartDtl_NextJobPO].[PONum] as [PartDtl_NextJobPO_PONum],
	[PartDtl_NextJobPO].[DueDate] as [PartDtl_NextJobPO_DueDate],
	[PartDtl_NextJobPO].[Quantity] as [PartDtl_NextJobPO_Quantity],
	[POHeader].[Confirmed] as [POHeader_Confirmed],
	[JobHead1].[JobReleased] as [JobHead1_JobReleased],
	(case 
     when PartDtl_NextJobPO.PONum = 0 then PartDtl_NextJobPO.JobNum 
      
     else convert(varchar,PartDtl_NextJobPO.PONum )  
     
 end) as [Calculated_PO_JOB],
	(case 
     when PartDtl_NextJobPO.PONum = 0 then JobHead1.JobReleased 
      
     else POHeader.Confirmed 
     
 end) as [Calculated_Confirm_Released]
from Erp.PartDtl as PartDtl_NextJobPO
left outer join Erp.POHeader as POHeader on 
	PartDtl_NextJobPO.Company = POHeader.Company
	and PartDtl_NextJobPO.PONum = POHeader.PONum
left outer join Erp.JobHead as JobHead1 on 
	PartDtl_NextJobPO.Company = JobHead1.Company
	and PartDtl_NextJobPO.JobNum = JobHead1.JobNum
where (PartDtl_NextJobPO.RequirementFlag = 0  and PartDtl_NextJobPO.Type = 'mtl'))  as PartDtl_NxtJobPO on 
	PartDtl_NxtJobPO.PartDtl_NextJobPO_Company = OrderRel.Company
	and PartDtl_NxtJobPO.PartDtl_NextJobPO_PartNum = OrderRel.PartNum
	and ( PartDtl_NxtJobPO.Calculated_LineNum = 1  )

left outer join  (select 
	[JobAsmbl].[Company] as [JobAsmbl_Company],
	[JobAsmbl].[JobNum] as [JobAsmbl_JobNum],
	(SUM(JobAsmbl.TLESetupHours+ JobAsmbl.TLEProdHours)) as [Calculated_EST_Hours],
	(SUM(JobAsmbl.TLASetupHours+ JobAsmbl.TLAProdHours)) as [Calculated_ACT_Hours]
from Erp.JobAsmbl as JobAsmbl
where (JobAsmbl.JobComplete <> 1)
group by [JobAsmbl].[Company],
	[JobAsmbl].[JobNum])  as JobASM on 
	JobASM.JobAsmbl_Company = PartDtl_NxtJobPO.PartDtl_NextJobPO_Company
	and JobASM.JobAsmbl_JobNum = PartDtl_NxtJobPO.PartDtl_NextJobPO_JobNum
inner join Erp.Currency as Currency on 
	OrderDtl.Company = Currency.Company
inner join Erp.Customer as Customer on 
	OrderHed.Company = Customer.Company
	and OrderHed.BTCustNum = Customer.CustNum
where (OrderHed.OpenOrder = 1)
order by OrderRel.ReqDate

[jgiese.wci (Joshua Giese)]

Try removing the company link between customer and orderhed there is a known bug in 600 that can get you there. Add a condition on customer with company = CurrentCompany.

See what you get.

[Andrewpech (Andy Pech)]

Tried some of the customer links. Looks like it hates what I’m trying to do with the part DTL subquery to return the first ranked row (next due PO / Job Num)

I’ll try to adjust some things around.

[John_Mitchell]

I would try to use a BAQ Parameter that you can pass to all of your subqueries to limit the table scans for each subquery. I would assume that you are doing full table scans on some really big tables right now.

[Andrewpech (Andy Pech)]

You mean like a open = 1 inside the subquery, or the TOP?

I think I got most of them looking at a specific set, but I’ll doublecheck it all.

[gpayne (Greg Payne)]

@Andrewpech Another thread on this kind of issue below. Things to try. Check the execution plan in SSMS. I had to add an index for a baq and it made a huge difference. Also, option recompile could help.

If it runs well with PartDtl out then you could try to get the next PO in code post processing on getdtl instead.

Strange, Poor BAQ Performance?

I’ve noticed recently on my 10.2.600.5 instance some very odd and poor performance on BAQs with seemingly innocuous things. For example, take this query. [image] If I include the table restraint on Part.ShortChar01, the query times out and will not run. If I exclude it, the query runs in under 5 seconds. [image] I had a different query that timed out if I put date parameters on the InvoiceDate field. Running the query WIDE open in a dashboard to return 30K+ rows takes a couple seconds. B…

[Andrewpech (Andy Pech)]

BAQs make me want to day drink…

Just needed to dump a couple subqueries from the phrase build table and re-add.

[cfinley (Chance Finley)]

Perhaps you had a bad cached SQL Execution plan? Odd. So you are saying that you deleted the subqueries and re-added identically?

[LarsonSolutions (Bruce Larson)]

I have found that open joins for the
Customer to Order head, currency to OrderDtl, part plant to orderrel, etc. actual will speed up the query.

Also, try to use PartDtl, it might help with some of the information required and reduce some of your sub-queries.

[Andrewpech (Andy Pech)]

Not really even the subqueries… But just the subquery tile on the TOP query.
I checked 20 times to make sure I didn’t have something funky in the relationship.

[Andrewpech (Andy Pech)]

@LarsonSolutions

use PartDtl, it might help with some of the information required and reduce some of your sub-queries.

Agree. PartDtl is such a wonderous table! Its central to a lot of the queries I’m writing lately.