BAQ Timeout

,

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.

@knash

It is not a difficult query. 2026 rows are being returned and there are 44 columns.

could you post the query? or BAQ

@knash

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.

@knash

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.