BAQ Assistance

Epicor ERP 10
,
1

I have a BAQ that works throughout most of the week, but every Sunday through Monday it seems to just freeze and I cannot figure out where or why. I have troubleshot this thing, subquery by subquery. Everything seems to be working just fine until we hit the top level and then it just times out. I have adjusted the timeout for it to be 500000 ms, but it gives the below timeout message:

image
image862×106 4.17 KB

Funny thing is if I wait till Tuesday it works just fine the rest of the week. Below is the phrase build. Any and all assistance is appreciated.

/*

  • 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
[SubQuery4].[LaborHed_Company] as [LaborHed_Company],
(isnull(sum(SubQuery5.Calculated_Calc_LaborHrs),0)) as [Calculated_SumLbrHrs],
(isnull(sum(SubQuery4.Calculated_Sum_PayHrs),0)) as [Calculated_SumPayHrs],
[SubQuery4].[EmpBasic3_JCDept] as [EmpBasic3_JCDept],
[SubQuery4].[LaborHed_PayrollDate] as [LaborHed_PayrollDate],
[SubQuery4].[EmpBasic3_Name] as [EmpBasic3_Name],
(isnull(sum(YearIndHrs2.Calculated_SumYearIndHrs),0)) as [Calculated_SumIndHrs]
from (select
(sum(LaborHed.PayHours)) as [Calculated_Sum_PayHrs],
[LaborHed].[Company] as [LaborHed_Company],
[EmpBasic3].[JCDept] as [EmpBasic3_JCDept],
[LaborHed].[PayrollDate] as [LaborHed_PayrollDate],
[EmpBasic3].[Name] as [EmpBasic3_Name]
from Erp.LaborHed as LaborHed
inner join Erp.EmpBasic as EmpBasic3 on
LaborHed.Company = EmpBasic3.Company
and LaborHed.EmployeeNum = EmpBasic3.EmpID
and ( not EmpBasic3.JCDept = ‘Admin’ and not EmpBasic3.JCDept = ‘ProdSupp’ and EmpBasic3.EmpStatus = ‘A’ and not EmpBasic3.JCDept = ‘Office’ and not EmpBasic3.JCDept = ‘Maint’ and not EmpBasic3.JCDept = ‘AEG’ )

where (LaborHed.PayrollDate >= DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0) and not LaborHed.EmployeeNum = ‘000120’ and not LaborHed.EmployeeNum = ‘000123’ and not LaborHed.EmployeeNum = ‘000132’ and not LaborHed.EmployeeNum = ‘000102’ and not LaborHed.EmployeeNum = ‘000202’ and not LaborHed.EmployeeNum = ‘010039’ and not LaborHed.EmployeeNum = ‘010117’ and not LaborHed.EmployeeNum = ‘010132’ and not LaborHed.EmployeeNum = ‘010203’ and not LaborHed.EmployeeNum = ‘010147’ and not LaborHed.EmployeeNum = ‘010224’ and not LaborHed.EmployeeNum = ‘010199’ and not LaborHed.EmployeeNum = ‘010165’ and not LaborHed.EmployeeNum = ‘010006’ and not LaborHed.EmployeeNum = ‘010211’ and not LaborHed.EmployeeNum = ‘010123’ and not LaborHed.EmployeeNum = ‘010201’ and not LaborHed.EmployeeNum = ‘010186’)
group by [LaborHed].[Company],
[EmpBasic3].[JCDept],
[LaborHed].[PayrollDate],
[EmpBasic3].[Name]) as SubQuery4
left outer join (select
(sum(LaborDtl.LaborHrs)) as [Calculated_Calc_LaborHrs],
[LaborDtl].[Company] as [LaborDtl_Company],
[EmpBasic2].[JCDept] as [EmpBasic2_JCDept],
[LaborDtl].[PayrollDate] as [LaborDtl_PayrollDate],
[EmpBasic2].[Name] as [EmpBasic2_Name]
from Erp.LaborDtl as LaborDtl
inner join Erp.EmpBasic as EmpBasic2 on
LaborDtl.Company = EmpBasic2.Company
and LaborDtl.EmployeeNum = EmpBasic2.EmpID
and ( not EmpBasic2.JCDept = ‘Admin’ and not EmpBasic2.JCDept = ‘ProdSupp’ and EmpBasic2.EmpStatus = ‘A’ )

where (LaborDtl.LaborType <> ‘I’ and LaborDtl.PayrollDate >= DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0) and not LaborDtl.EmployeeNum = ‘000120’ and not LaborDtl.EmployeeNum = ‘000123’ and not LaborDtl.EmployeeNum = ‘000132’ and not LaborDtl.EmployeeNum = ‘000102’ and not LaborDtl.EmployeeNum = ‘000202’ and not LaborDtl.EmployeeNum = ‘010039’ and not LaborDtl.EmployeeNum = ‘010117’ and not LaborDtl.EmployeeNum = ‘010132’ and not LaborDtl.EmployeeNum = ‘010203’ and not LaborDtl.EmployeeNum = ‘010147’ and not LaborDtl.EmployeeNum = ‘010224’ and not LaborDtl.EmployeeNum = ‘010199’ and not LaborDtl.EmployeeNum = ‘010165’ and not LaborDtl.EmployeeNum = ‘010006’ and not LaborDtl.EmployeeNum = ‘010211’ and not LaborDtl.EmployeeNum = ‘010123’ and not LaborDtl.EmployeeNum = ‘010201’ and not LaborDtl.EmployeeNum = ‘010186’)
group by [LaborDtl].[Company],
[EmpBasic2].[JCDept],
[LaborDtl].[PayrollDate],
[EmpBasic2].[Name]) as SubQuery5 on
SubQuery4.LaborHed_Company = SubQuery5.LaborDtl_Company
and SubQuery4.EmpBasic3_JCDept = SubQuery5.EmpBasic2_JCDept
and SubQuery4.LaborHed_PayrollDate = SubQuery5.LaborDtl_PayrollDate
and SubQuery4.EmpBasic3_Name = SubQuery5.EmpBasic2_Name
left outer join (select
[LaborDtl2].[PayrollDate] as [LaborDtl2_PayrollDate],
[LaborDtl2].[Company] as [LaborDtl2_Company],
[EmpBasic4].[JCDept] as [EmpBasic4_JCDept],
[EmpBasic4].[Name] as [EmpBasic4_Name],
(sum(LaborDtl2.LaborHrs)) as [Calculated_SumYearIndHrs],
[LaborDtl2].[EmployeeNum] as [LaborDtl2_EmployeeNum],
[EmpBasic4].[EmpID] as [EmpBasic4_EmpID]
from Erp.LaborDtl as LaborDtl2
inner join Erp.EmpBasic as EmpBasic4 on
LaborDtl2.Company = EmpBasic4.Company
and LaborDtl2.EmployeeNum = EmpBasic4.EmpID
and ( not EmpBasic4.JCDept = ‘Admin’ and not EmpBasic4.JCDept = ‘ProdSupp’ and EmpBasic4.EmpStatus = ‘A’ )

where (LaborDtl2.LaborType = ‘I’ and not LaborDtl2.IndirectCode = ‘IDLE’ and LaborDtl2.PayrollDate >= DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0) and not LaborDtl2.IndirectCode = ‘IND’)
group by [LaborDtl2].[PayrollDate],
[LaborDtl2].[Company],
[EmpBasic4].[JCDept],
[EmpBasic4].[Name],
[LaborDtl2].[EmployeeNum],
[EmpBasic4].[EmpID]) as YearIndHrs2 on
YearIndHrs2.EmpBasic4_JCDept = SubQuery4.EmpBasic3_JCDept
and YearIndHrs2.EmpBasic4_Name = SubQuery4.EmpBasic3_Name
and YearIndHrs2.LaborDtl2_Company = SubQuery4.LaborHed_Company
and YearIndHrs2.LaborDtl2_PayrollDate = SubQuery4.LaborHed_PayrollDate
group by [SubQuery4].[LaborHed_Company],
[SubQuery4].[EmpBasic3_JCDept],
[SubQuery4].[LaborHed_PayrollDate],
[SubQuery4].[EmpBasic3_Name]

2

Once the query has run within BAQ Designer, from the Actions menu you get the Query Execution Plan.

Download this file, and then open it using SQL Server Management Studio. It will highlight where the majority of the time is being spent. You can then look to improve the query in Epicor, or consider adding an index as suggested by the query plan analysis.

The trick is to try and hit Epicor native indexes - an example might be where you’re limiting based upon EmployeeNum, which doesn’t hit an index. If you add in another filter to check for Company = BAQ.CurComp then it might hit the default index and return results much quicker.

3 Likes
3

@markdamen,

I’ll work towards getting those optimizations made. In the mean time, is there something else I can do in order to increase timeout time? I guess I’m just confused as to why the query execution time isn’t matching what I set it too, 30k ms vs 500kms. I am on gov cloud if that info changes anything.

4

When you made that change, you set it to “persist in query”? Otherwise, it would apply for the BAQ Designer session but then be lost when it runs for real or you reload the designer.

In the Epicor Admin Console which you don’t have access to, there is also a setting regards BAQ execution. I’m not sure what the hierarchy is of how the 2 settings are applied, but it this:

image

You could raise a ticket and see if they have 30 seconds configured here?

2 Likes
5

@markdamen,

I had it set to persist in query. I submitted a ticket to see if the cloud team will extend the timeout for me as well. Waiting for a response. Thanks for the help!