BAQ Timing out but in SQL runs in less than 1 second

Epicor ERP 10
1

I have a BAQ that suddenly started running like a dog. The user was getting a timeout error after 30 seconds. I increased the querytimout so that I could just let it run until it was complete and it took just over 10 minutes.

So I copied the SQL code from the BAQ and pasted it into SQL Management Studio and executed the Query…it took less that 1 second to run.

Anyone seen something like this before? It is not a complex BAQ, 5 tables with simple joins and grouping plus 1 calculated field.

select
[Customer].[Name] as [Customer_Name],
[OrderRel].[Plant] as [OrderRel_Plant],
[OrderHed].[OrderNum] as [OrderHed_OrderNum],
[OrderDtl].[OrderLine] as [OrderDtl_OrderLine],
[OrderHed].[EntryPerson] as [OrderHed_EntryPerson],
[OrderDtl].[PartNum] as [OrderDtl_PartNum],
[OrderDtl].[IUM] as [OrderDtl_IUM],
[OrderDtl].[SalesUM] as [OrderDtl_SalesUM],
[OrderDtl].[OrderQty] as [OrderDtl_OrderQty],
[PartPlant].[OrderIncr_c] as [PartPlant_OrderIncr_c],
(OrderDtl.OrderQty % PartPlant.OrderIncr_c) as [Calculated_Modulus]
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 )

inner join Erp.OrderRel as OrderRel on
OrderDtl.Company = OrderRel.Company
and OrderDtl.OrderNum = OrderRel.OrderNum
and OrderDtl.OrderLine = OrderRel.OrderLine
inner join dbo.PartPlant as PartPlant on
PartPlant.Company = OrderRel.Company
and PartPlant.PartNum = OrderRel.PartNum
and PartPlant.Plant = OrderRel.Plant
and ( PartPlant.OrderIncr_c > 1 )

inner join Erp.Customer as Customer on
OrderHed.Company = Customer.Company
and OrderHed.BTCustNum = Customer.CustNum
where (OrderHed.OpenOrder = 1 and OrderHed.VoidOrder = 0)
and ((OrderDtl.OrderQty % PartPlant.OrderIncr_c) <> 0)

group by [Customer].[Name],
[OrderRel].[Plant],
[OrderHed].[OrderNum],
[OrderDtl].[OrderLine],
[OrderHed].[EntryPerson],
[OrderDtl].[PartNum],
[OrderDtl].[IUM],
[OrderDtl].[SalesUM],
[OrderDtl].[OrderQty],
[PartPlant].[OrderIncr_c],
(OrderDtl.OrderQty % PartPlant.OrderIncr_c)

2

@Brian_Chandler If you search there are several different threads on queries with customer being slower for a variety of reason as well as the wrapper the Epicor adds for territory security that make the SSMS query very different that what is in the BAQ.

Below is one approach to fix the issue. I think another one is removing company from the query.

3

You are using the customer table so like @gpayne said that automatically adds a ton of complexity you don’t see.

Few things to try (in this order I supose)
Remove Company from the Customer to Order table join (its silly and absurd but sometimes it helps)

Add Option Recompile at the end of your query (forces a new plan)
To do this go to your Display tab, click on Advanced Group by find the very last Group By and add this right after the last group by field in the expression

(OrderDtl.OrderQty % PartPlant.OrderIncr_c) OPTION(RECOMPILE)

Update your table statistics for all inolved tables full table scan please

UPDATE STATISTICS Erp.Customer WITH FULLSCAN
UPDATE STATISTICS Erp.OrderHed WITH FULLSCAN
UPDATE STATISTICS Erp.OrderDtl WITH FULLSCAN
UPDATE STATISTICS Erp.PartPlant WITH FULLSCAN

None of the above should hurt anything but obviously handle with care. Also read on the related posts there may be other suggestions.

1 Like
4

I have recently seen some dramatic improvements in BAQ performance by changing some inner joins to outer joins

5

Adding the OPTION (Recompile) worked, thanks for the input.

1 Like
6

If my BAQ doesnt have any Group By clause (it ends with a inner join, no where clause), how to add the OPTION(RECOMPILE)?

thanks,

1 Like
7

opps

answered wrong question.