BAQ Timeout (Orderby)

Evan_Purdy · June 27, 2018, 11:57am

A BAQ someone else wrote is suddenly timing out today. I can get it to work (though it still takes a looong time) if I remove the all the Order By clauses at the end. Any idea how I can get this running fast enough to not time out again?

select 
	[Customer].[Name] as [Customer_Name],
	[OrderDtl].[OrderNum] as [OrderDtl_OrderNum],
	[OrderDtl].[OrderLine] as [OrderDtl_OrderLine],
	[OrderRel].[OrderRelNum] as [OrderRel_OrderRelNum],
	[JobProd].[JobNum] as [JobProd_JobNum],
	[OrderDtl].[PartNum] as [OrderDtl_PartNum],
	[OrderDtl].[LineDesc] as [OrderDtl_LineDesc],
	[JobProd].[ProdQty] as [JobProd_ProdQty],
	[JobHead].[QtyCompleted] as [JobHead_QtyCompleted],
	[OrderDtl].[OrderQty] as [OrderDtl_OrderQty],
	[OrderRel].[OurReqQty] as [OrderRel_OurReqQty],
	[OrderRel].[OpenRelease] as [OrderRel_OpenRelease],
	[ProdGrup].[Description] as [ProdGrup_Description],
	[OrderHed].[OrderDate] as [OrderHed_OrderDate],
	[OrderHed].[EntryPerson] as [OrderHed_EntryPerson],
	[JobHead].[CreateDate] as [JobHead_CreateDate],
	[JobHead].[CreatedBy] as [JobHead_CreatedBy],
	[ShipVia].[Description] as [ShipVia_Description],
	[OrderRel].[ShipViaCode] as [OrderRel_ShipViaCode],
	[OrderDtl].[OpenLine] as [OrderDtl_OpenLine],
	[OrderHed].[PONum] as [OrderHed_PONum],
	[OrderHed].[OrderHeld] as [OrderHed_OrderHeld],
	[OrderRel].[BuyToOrder] as [OrderRel_BuyToOrder],
	[OrderRel].[DropShip] as [OrderRel_DropShip],
	[Vendor].[Name] as [Vendor_Name],
	[POHeader].[PONum] as [POHeader_PONum],
	[POHeader].[OrderDate] as [POHeader_OrderDate],
	[JobHead].[ReqDueDate] as [JobHead_ReqDueDate],
	[OrderDtl].[NeedByDate] as [OrderDtl_NeedByDate],
	[OrderRel].[NeedByDate] as [OrderRel_NeedByDate],
	[OrderRel].[ShipToNum] as [OrderRel_ShipToNum],
	[ShipTo].[Address1] as [ShipTo_Address1],
	[OrderHed].[ShipComment] as [OrderHed_ShipComment],
	[ShipDtl].[PackNum] as [ShipDtl_PackNum],
	[ShipDtl].[PackLine] as [ShipDtl_PackLine]
from Erp.OrderDtl as OrderDtl
left outer join Erp.OrderRel as OrderRel on 
	OrderDtl.Company = OrderRel.Company
	and OrderDtl.OrderNum = OrderRel.OrderNum
	and OrderDtl.OrderLine = OrderRel.OrderLine
	and ( OrderRel.ReqDate >= '01/01/2017'  and OrderRel.OpenRelease = 1  )

left outer join Erp.JobProd as JobProd on 
	OrderRel.Company = JobProd.Company
	and OrderRel.OrderNum = JobProd.OrderNum
	and OrderRel.OrderLine = JobProd.OrderLine
	and OrderRel.OrderRelNum = JobProd.OrderRelNum
left outer join Erp.JobHead as JobHead on 
	JobProd.Company = JobHead.Company
	and JobProd.JobNum = JobHead.JobNum
left outer join Erp.Vendor as Vendor on 
	OrderRel.Company = Vendor.Company
	and OrderRel.VendorNum = Vendor.VendorNum
left outer join Erp.POHeader as POHeader on 
	OrderRel.Company = POHeader.Company
	and OrderRel.PONum = POHeader.PONum
left outer join Erp.ShipVia as ShipVia on 
	OrderRel.Company = ShipVia.Company
	and OrderRel.ShipViaCode = ShipVia.ShipViaCode
left outer join Erp.ShipTo as ShipTo on 
	OrderRel.Company = ShipTo.Company
	and OrderRel.ShipToNum = ShipTo.ShipToNum
inner join Erp.Customer as Customer on 
	Customer.Company = ShipTo.Company
	and Customer.CustNum = ShipTo.CustNum
inner join Erp.OrderHed as OrderHed on 
	OrderDtl.Company = OrderHed.Company
	and OrderDtl.OrderNum = OrderHed.OrderNum
inner join Erp.OrderHed as OrderHed
	and 
	OrderHed.Company = Customer.Company
	and OrderHed.CustNum = Customer.CustNum
left outer join Erp.ShipDtl as ShipDtl on 
	ShipDtl.Company = OrderRel.Company
	and ShipDtl.OrderNum = OrderRel.OrderNum
	and ShipDtl.OrderLine = OrderRel.OrderLine
	and ShipDtl.OrderRelNum = OrderRel.OrderRelNum
left outer join Erp.ShipDtl as ShipDtl
	and 
	Customer.Company = ShipDtl.Company
	and Customer.CustNum = ShipDtl.CustNum
left outer join Erp.ProdGrup as ProdGrup on 
	OrderDtl.Company = ProdGrup.Company
	and OrderDtl.ProdCode = ProdGrup.ProdCode
where (OrderDtl.OpenLine = 1)
order by ProdGrup.Description, OrderRel.NeedByDate, OrderHed.OrderNum, OrderDtl.OrderLine, OrderRel.OrderRelNum

tfenwick11 · June 27, 2018, 2:40pm

After reviewing the query, it looks like most of your slowness and duplication problems where because of the join between “ShipTo” and “OrderRel” on ShipToNum. When testing in my database I found for example that (OrderRel.OrderNum = ‘1234’) had several lines with the same ship to value = ‘1’. Where as in “ShipTo” there was four thousand customers with the ShipToNum = ‘1’. So I referenced OrderHed.CustNum to Customer.CustNum to reference the customer name and its address.

Hopefully the rewritten version below helps you out as its more organized and in a tree structure for the columns as you would want to see your results at a drill down level. Such as OrderHed>>OrdetDtl>>OrderRel…etc. After my testing there were no duplicated records per order when considering line details.

Unfortunately you will have to translate this into a BAQ.

SELECT
  C.[Name] AS Customer_Name
 ,OH.OrderNum
 ,OH.OrderDate
 ,OH.EntryPerson
 ,OH.PONum AS OH_PO_NUM
 ,OH.OrderHeld
 ,OH.ShipComment
 ,ODTL.NeedByDate AS ODTL_Need_By_Date
 ,ODTL.OrderLine
 ,ODTL.OpenLine
 ,ODTL.PartNum
 ,ODTL.LineDesc
 ,ODTL.OrderQty
 ,ODRL.OrderRelNum
 ,ODRL.OurReqQty
 ,ODRL.OpenRelease
 ,ODRL.ShipViaCode
 ,ODRL.BuyToOrder
 ,ODRL.DropShip
 ,ODRL.NeedByDate AS ODRL_Need_By_Date
 ,ODRL.ShipToNum
 ,JH.JobNum
 ,JH.ProdQty
 ,JH.QtyCompleted
 ,JH.CreateDate
 ,JH.CreatedBy
 ,JH.ReqDueDate
 ,POH.PONum
 ,POH.OrderDate AS PO_Order_Date
 ,PRODG.[Description] AS Prod_Group_Description
 ,V.[Name] AS Vendor_Name
 ,SPV.[Description] AS Ship_Via_Description
 ,ShipDtl.PackNum
 ,ShipDtl.PackLine


FROM Erp.OrderHed (NOLOCK)  AS OH 
INNER JOIN Erp.OrderDtl (NOLOCK) AS ODTL
    ON OH.Company = ODTL.Company
	   AND OH.OrderNum = ODTL.OrderNum

INNER JOIN Erp.OrderRel (NOLOCK) AS ODRL
   ON ODTL.Company = ODRL.Company
	AND ODTL.OrderNum = ODRL.OrderNum
	AND ODTL.OrderLine = ODRL.OrderLine
	AND ODRL.ReqDate >= '01/01/2017' 
	AND ODRL.OpenRelease = 1

LEFT OUTER JOIN Erp.JobProd (NOLOCK)  AS JP 
  ON ODRL.Company = JP.Company
	AND ODRL.OrderNum = JP.OrderNum
	AND ODRL.OrderLine = JP.OrderLine
	AND ODRL.OrderRelNum = JP.OrderRelNum

LEFT OUTER JOIN Erp.JobHead (NOLOCK)  AS JH 
  ON JP.Company = JH.Company
	AND JP.JobNum = JH.JobNum

LEFT OUTER JOIN Erp.Vendor (NOLOCK)  AS V 
  ON ODRL.Company = V.Company
	AND ODRL.VendorNum = V.VendorNum

LEFT OUTER JOIN Erp.POHeader (NOLOCK)  AS POH  
  ON ODRL.Company = POH.Company
	AND ODRL.PONum = POH.PONum

LEFT OUTER JOIN Erp.ShipVia (NOLOCK)  AS SPV
  ON	ODRL.Company = SPV.Company
	AND ODRL.ShipViaCode = SPV.ShipViaCode

--LEFT OUTER JOIN Erp.ShipTo (NOLOCK)  AS SHPT 
--  ON ODRL.Company = SHPT.Company
--	AND ODRL.ShipToNum = SHPT.ShipToNum  

INNER JOIN Erp.Customer (NOLOCK)  AS C  /*Replaces Ship to as its not needed*/
  ON C.Company = OH.Company
	AND C.CustNum = OH.CustNum

INNER JOIN Erp.ShipDtl (NOLOCK) AS ShipDtl
   ON ShipDtl.Company = ODRL.Company
	AND ShipDtl.OrderNum = ODRL.OrderNum
	AND ShipDtl.OrderLine = ODRL.OrderLine
	AND ShipDtl.OrderRelNum = ODRL.OrderRelNum

LEFT OUTER JOIN Erp.ShipDtl (NOLOCK)  AS SHIPDTLC
   ON OH.Company = SHIPDTLC.Company
   AND OH.OrderNum = SHIPDTLC.OrderNum
	AND ODTL.PartNum = SHIPDTLC.PartNum

INNER JOIN Erp.ProdGrup (NOLOCK)  AS PRODG on 
	ODTL.Company = PRODG.Company
	AND ODTL.ProdCode = PRODG.ProdCode

WHERE ODTL.OpenLine = 1

Evan_Purdy · June 27, 2018, 6:02pm

Hmm, I think I managed to make the equivalent BAQ. It seems like there are orders missing though, but I can’t figure out why. It took 31162 ms for 50 rows, but there should be quite a bit more…

select 
	[Customer].[Name] as [Customer_Name],
	[OrderHed].[OrderNum] as [OrderHed_OrderNum],
	[OrderHed].[OrderDate] as [OrderHed_OrderDate],
	[OrderHed].[EntryPerson] as [OrderHed_EntryPerson],
	[OrderHed].[PONum] as [OrderHed_PONum],
	[OrderHed].[OrderHeld] as [OrderHed_OrderHeld],
	[OrderHed].[ShipComment] as [OrderHed_ShipComment],
	[OrderDtl].[NeedByDate] as [OrderDtl_NeedByDate],
	[OrderDtl].[OrderLine] as [OrderDtl_OrderLine],
	[OrderDtl].[OpenLine] as [OrderDtl_OpenLine],
	[OrderDtl].[PartNum] as [OrderDtl_PartNum],
	[OrderDtl].[LineDesc] as [OrderDtl_LineDesc],
	[OrderDtl].[OrderQty] as [OrderDtl_OrderQty],
	[OrderRel].[OrderRelNum] as [OrderRel_OrderRelNum],
	[OrderRel].[OurReqQty] as [OrderRel_OurReqQty],
	[OrderRel].[OpenRelease] as [OrderRel_OpenRelease],
	[OrderRel].[ShipViaCode] as [OrderRel_ShipViaCode],
	[OrderRel].[BuyToOrder] as [OrderRel_BuyToOrder],
	[OrderRel].[DropShip] as [OrderRel_DropShip],
	[OrderRel].[NeedByDate] as [OrderRel_NeedByDate],
	[OrderRel].[ShipToNum] as [OrderRel_ShipToNum],
	[JobHead].[JobNum] as [JobHead_JobNum],
	[JobHead].[ProdQty] as [JobHead_ProdQty],
	[JobHead].[QtyCompleted] as [JobHead_QtyCompleted],
	[JobHead].[CreateDate] as [JobHead_CreateDate],
	[JobHead].[CreatedBy] as [JobHead_CreatedBy],
	[JobHead].[ReqDueDate] as [JobHead_ReqDueDate],
	[POHeader].[PONum] as [POHeader_PONum],
	[POHeader].[OrderDate] as [POHeader_OrderDate],
	[ProdGrup].[Description] as [ProdGrup_Description],
	[Vendor].[Name] as [Vendor_Name],
	[ShipVia].[Description] as [ShipVia_Description],
	[ShipDtl].[PartNum] as [ShipDtl_PartNum],
	[ShipDtl].[PackLine] as [ShipDtl_PackLine]
from Erp.OrderHed as OrderHed
inner join Erp.OrderDtl as OrderDtl on 
	OrderHed.Company = OrderDtl.Company
	and OrderHed.OrderNum = OrderDtl.OrderNum
inner join Erp.OrderRel as OrderRel on 
	OrderDtl.Company = OrderRel.Company
	and OrderDtl.OrderNum = OrderRel.OrderNum
	and OrderDtl.OrderLine = OrderRel.OrderLine
	and ( OrderRel.ReqDate >= '01/01/2018'  and OrderRel.OpenRelease = 1  )

left outer join Erp.JobProd as JobProd on 
	OrderRel.Company = JobProd.Company
	and OrderRel.OrderNum = JobProd.OrderNum
	and OrderRel.OrderLine = JobProd.OrderLine
	and OrderRel.OrderRelNum = JobProd.OrderRelNum
left outer join Erp.JobHead as JobHead on 
	JobProd.Company = JobHead.Company
	and JobProd.JobNum = JobHead.JobNum
left outer join Erp.Vendor as Vendor on 
	OrderRel.Company = Vendor.Company
	and OrderRel.VendorNum = Vendor.VendorNum
left outer join Erp.POHeader as POHeader on 
	OrderRel.Company = POHeader.Company
	and OrderRel.PONum = POHeader.PONum
left outer join Erp.ShipVia as ShipVia on 
	OrderRel.Company = ShipVia.Company
	and OrderRel.ShipViaCode = ShipVia.ShipViaCode
inner join Erp.ShipDtl as ShipDtl on 
	OrderDtl.Company = ShipDtl.Company
	and OrderDtl.OrderNum = ShipDtl.OrderNum
	and OrderDtl.OrderLine = ShipDtl.OrderLine
inner join Erp.ProdGrup as ProdGrup on 
	OrderDtl.Company = ProdGrup.Company
	and OrderDtl.ProdCode = ProdGrup.ProdCode
inner join Erp.ShipDtl as ShipDtlC on 
	ShipDtlC.Company = OrderHed.Company
	and ShipDtlC.OrderNum = OrderHed.OrderNum
inner join Erp.ShipDtl as ShipDtlC
	and 
	OrderDtl.Company = ShipDtlC.Company
	and OrderDtl.OrderNum = ShipDtlC.OrderNum
	and OrderDtl.OrderLine = ShipDtlC.OrderLine
	and OrderDtl.PartNum = ShipDtlC.PartNum
inner join Erp.Customer as Customer on 
	OrderHed.Company = Customer.Company
	and OrderHed.CustNum = Customer.CustNum
where OrderDtl.OpenLine = 1
order by ProdGrup.Description, OrderRel.NeedByDate, OrderHed.OrderNum, OrderDtl.OrderLine, OrderRel.OrderRelNum

tfenwick11 · June 27, 2018, 6:26pm

Your OrderRel.ReqDate is 2018 instead of 2017. But I would reference an order you think is not in the new BAQ results and compare its information to the results the BAQ is returning.

Evan_Purdy · June 27, 2018, 6:28pm

Yeah, I will do that. Its not the date for at least some that I checked… Looks like it might be outsourced orders or something…

Evan_Purdy · June 27, 2018, 7:56pm

Ok, I found one variable… Any row where Ship Via is “Best Way” is being omitted.

There should be 170 rows, I am getting 60 rows. Those missing “Best Way” rows account for 33 missing rows but that leaves 76 left so I guess I need to figure out what the deal is with those.

Evan_Purdy · June 28, 2018, 7:30pm

So the results I am comparing it to are from the query in my OP, but with the sorting taken out. I can’t any difference in the two queries that should be excluding anything based off “Ship Via”… can you?

tfenwick11 · June 28, 2018, 7:39pm

In your previous post you said any row where ship via is “Best Way” is being omitted. What is causing them to be omitted? Is your order releases “ship via code” not exist in the ship via table? Ill be honest if you want to find the exclusions try running the query per join until you find the differences instead of trying to diagnose it as a whole. Not trying to be difficult. Its just kind of hard to understand your exclusions without seeing the data.

Evan_Purdy · June 28, 2018, 7:41pm

Is there an easy way to just disable the BAQ blocks without deleting them? That would make it easier to troubleshoot…

tfenwick11 · June 28, 2018, 7:59pm

To be honest I don’t know. I hardly spend anytime in the BAQ designer. I write all my reports externally and host them in E10.

Evan_Purdy · June 28, 2018, 8:02pm

Well as a SaaS cloud multi-tenant user that is not an option as far as I know…

On the plus side I noticed you had commented out the ShipTo join, which I didn’t notice before. Removing that made it a ton faster, though its still missing the same amount.

Topic		Replies	Views
BAQ Help Epicor ERP 10 e10 , baq	2	296	August 26, 2020
BAQ Timeout Epicor ERP 10 e10 , baq	11	5580	August 10, 2017
BAQ Timing out but in SQL runs in less than 1 second Epicor ERP 10 baq	6	1498	August 24, 2020
BAQ TEST Performance Epicor ERP 10 baq	12	2114	March 24, 2022
BAQ Timeout - Why? Epicor ERP 10 baq	11	2413	February 25, 2019

BAQ Timeout (Orderby)

Related Topics