Why would a BAQ run so long it times out but if i take the SQL generated by the BAQ and run it in Studio Manager it runs in under a second?
You mean the sql manager? How many records are being returned? How many columns?
The more data being returned will increase the time as it needs to go through the App Server vs Directly from the database.
could you post the query? or BAQ
Here it is:
select
[OrderRel].[NeedByDate] as [OrderRel_NeedByDate],
[OrderRel].[ReqDate] as [OrderRel_ReqDate],
[Customer].[CustID] as [Customer_CustID],
[Customer].[Name] as [Customer_Name],
[Customer].[SalesRepCode] as [Customer_SalesRepCode],
[OrderRel].[OrderNum] as [OrderRel_OrderNum],
[OrderRel].[OrderLine] as [OrderRel_OrderLine],
[OrderRel].[OrderRelNum] as [OrderRel_OrderRelNum],
[OrderHed].[PONum] as [OrderHed_PONum],
REPLACE(REPLACE([OrderHed].[OrderComment], CHAR(13), ''), CHAR(10), '') as [OrderHed_OrderComment],
[OrderRel].[PartNum] as [OrderRel_PartNum],
[OrderRel].[OurJobShippedQty] as [OrderRel_OurJobShippedQty],
[OrderRel].[OurStockShippedQty] as [OrderRel_OurStockShippedQty],
[OrderRel].[Make] as [OrderRel_Make],
[JobProd].[JobNum] as [JobProd_JobNum],
[JobProd].[ProdQty] as [JobProd_ProdQty],
[JobHead].[QtyCompleted] as [JobHead_QtyCompleted],
[JobHead].[JobEngineered] as [JobHead_JobEngineered],
[JobHead].[JobReleased] as [JobHead_JobReleased],
[ShipDtl].[PackNum] as [ShipDtl_PackNum],
[ShipDtl].[PackLine] as [ShipDtl_PackLine],
[ShipHead].[ShipStatus] as [ShipHead_ShipStatus],
[ShipHead].[ShipDate] as [ShipHead_ShipDate],
[OrderDtl].[UnitPrice] as [OrderDtl_UnitPrice],
[OrderDtl].[DiscountPercent] as [OrderDtl_DiscountPercent],
[ShipDtl].[OurInventoryShipQty] as [ShipDtl_OurInventoryShipQty],
[ShipDtl].[OurJobShipQty] as [ShipDtl_OurJobShipQty],
(ShipDtl.OurInventoryShipQty + ShipDtl.OurJobShipQty) as [Calculated_TotalShipped],
[OrderDtl].[OrdBasedPrice] as [OrderDtl_OrdBasedPrice],
(OrderDtl.OrdBasedPrice * TotalShipped) as [Calculated_ShipLineValue],
[OrderHed].[OrderHeld] as [OrderHed_OrderHeld],
[Customer].[CreditHold] as [Customer_CreditHold],
[OrderHed].[CreditOverride] as [OrderHed_CreditOverride],
[OrderHed].[ShipViaCode] as [OrderHed_ShipViaCode],
[ShipHead].[ShipViaCode] as [ShipHead_ShipViaCode],
(convert(varchar, OrderRel.OrderNum) + '-' + convert(varchar, OrderRel.OrderLine) + '-' + convert(varchar, OrderRel.OrderRelNum)) as [Calculated_CalcOrderLineRel],
[OrderRel].[OurReqQty] as [OrderRel_OurReqQty],
[OrderDtl].[ProdCode] as [OrderDtl_ProdCode],
[SalesRep].[Name] as [SalesRep_Name],
[OrderRel].[Company] as [OrderRel_Company],
[ShipDtl].[JobNum] as [ShipDtl_JobNum],
[ShipDtl].[SellingInventoryShipQty] as [ShipDtl_SellingInventoryShipQty],
[ShipDtl].[SellingJobShipQty] as [ShipDtl_SellingJobShipQty]
from Erp.OrderRel as OrderRel
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.ShipDtl as ShipDtl on
OrderRel.Company = ShipDtl.Company
And
OrderRel.OrderNum = ShipDtl.OrderNum
And
OrderRel.OrderLine = ShipDtl.OrderLine
And
OrderRel.OrderRelNum = ShipDtl.OrderRelNum
left outer join Erp.ShipHead as ShipHead on
ShipDtl.Company = ShipHead.Company
And
ShipDtl.PackNum = ShipHead.PackNum
left outer join Erp.OrderHed as OrderHed on
OrderRel.Company = OrderHed.Company
And
OrderRel.OrderNum = OrderHed.OrderNum
inner join Erp.Customer as Customer on
OrderHed.Company = Customer.Company
And
OrderHed.CustNum = Customer.CustNum
left outer join Erp.SalesRep as SalesRep on
Customer.Company = SalesRep.Company
And
Customer.SalesRepCode = SalesRep.SalesRepCode
left outer join Erp.OrderDtl as OrderDtl on
OrderRel.Company = OrderDtl.Company
And
OrderRel.OrderNum = OrderDtl.OrderNum
And
OrderRel.OrderLine = OrderDtl.OrderLine
where (OrderRel.OpenRelease = 1 and OrderRel.VoidRelease = 0 and OrderRel.ReqDate <= '2017-09-23')
does it return records in the BAQ designer? Maybe try without the calculated fields… see if that helps.
It does not return records in the BAQ. It timesout. However, the strange thing is, if i add TOP to the query in the baq, it works.
The query should return 2026 records. It fails. If i modify the baq to return the top 5000, over the number of records, it works. … so yeah… (scratching head)
if you can export and post the BAQ Then I can check it out locally.
I’ve seen weird behavior like this with one of my external BAQs in 10.1.400. I have a workaround, but not a solution. External BAQ Timing out - #4 by kennethahayes - ERP 10 - Epicor User Help Forum
I ran into the same issue with one of my customer BAQs. I changed the QueryTimeout.
Go into your BAQ > Actions > Execution Settings : create a new setting > Choose “QueryTimeOut” and set it to some high number. Since i need to run the BAQ for my month end close analysis, i put in 50,000 for the setting value. Yes, it’s taxing on the server but if you can’t do without the baq, then it works.
Also, if you have more than 10,000 row, it will limit to 1st 10,000 rows unless you set the setting “RemoveTestRowLimit” to True
1 Like
Still doesnt answer the question to why are BAQs slower than SQL Queries yet behind the scenes they both do the exact same query. =) If anything they should be faster since ICE Framework would also do some caching etc… I would think so.
I have had that happen to me a long while back. I don’t remember exactly what I did but I changed the query in some way and it stopped being a problem. Pretty sure I had some subqueries in there that I changed.