LaborDtl BAQ takes forever

Epicor ERP 10
1

I am trying to write a BAQ to support a dashboard for our VP of finance that gets all of the labor records for all jobs closed in a month and it taking forever. I am wondering if I have the joins right. Here is the SQL:

SELECT 
	LD.EmployeeNum, 
	LD.LaborType, 
	LD.LaborTypePseudo, 
	LD.ReWork, 
	LD.JobNum, 
	LD.AssemblySeq, 
	LD.OprSeq, 
	LD.JCDept, 
	jc.Description AS DeptDescription,
	LD.ResourceGrpID, 
	rg.Description AS ResourceGroupDescription,
	LD.OpCode, 
	LD.LaborHrs, 
	LD.BurdenHrs, 
	LD.LaborQty, 
	LD.Complete, 
	LD.LaborNote, 
	LD.ExpenseCode, 
	LD.ClockInDate, 
	LD.LaborRate, 
	LD.BurdenRate, 
	LD.ResourceID, 
	LD.EarnedHrs, 
	LD.PostedToGL, 
	LD.GLTrans, 
	LD.WipPosted
FROM Erp.JobHead JH
JOIN Erp.JobOper JO ON JO.Company = JH.Company AND JO.JobNum = JH.JobNum 
JOIN Erp.JobOpDtl JOD ON JOD.Company = JO.Company AND JOD.JobNum = JO.JobNum AND JOD.OprSeq = JO.OprSeq AND JOD.AssemblySeq = JO.AssemblySeq 
JOIN Erp.LaborDtl LD ON LD.Company = JOD.Company AND LD.JobNum = JOD.JobNum AND LD.AssemblySeq = JOD.AssemblySeq AND LD.OprSeq = JOD.OprSeq 
JOIN Erp.JCDept JC ON JC.Company = LD.Company AND JC.JCDept = LD.JCDept 
JOIN Erp.ResourceGroup RG ON RG.Company = LD.Company AND RG.ResourceGrpID = LD.ResourceGrpID 
WHERE jh.JobClosed = 1
AND jh.ClosedDate BETWEEN '11/1/2021' AND '11/15/2021'

So these are closed jobs between a date range. Any ideas?

thanks

2

You are joining a lot of tables you aren’t using. try something like

SELECT 
	LD.EmployeeNum, 
	LD.LaborType, 
	LD.LaborTypePseudo, 
	LD.ReWork, 
	LD.JobNum, 
	LD.AssemblySeq, 
	LD.OprSeq, 
	LD.JCDept, 
	jc.Description AS DeptDescription,
	LD.ResourceGrpID, 
	rg.Description AS ResourceGroupDescription,
	LD.OpCode, 
	LD.LaborHrs, 
	LD.BurdenHrs, 
	LD.LaborQty, 
	LD.Complete, 
	LD.LaborNote, 
	LD.ExpenseCode, 
	LD.ClockInDate, 
	LD.LaborRate, 
	LD.BurdenRate, 
	LD.ResourceID, 
	LD.EarnedHrs, 
	LD.PostedToGL, 
	LD.GLTrans, 
	LD.WipPosted
FROM Erp.LaborDtl LD
inner JOIN Erp.ResourceGroup RG ON RG.Company = LD.Company AND RG.ResourceGrpID = LD.ResourceGrpID 
inner join Erp.JobHead JH on LD.Company = JH.Company AND LD.JobNum = JH.JobNum
inner JOIN Erp.JCDept JC ON JC.Company = LD.Company AND JC.JCDept = LD.JCDept 
WHERE jh.JobClosed = 1
AND jh.ClosedDate BETWEEN '11/1/2021' AND '11/15/2021'
2 Likes
3

Wow - that seems to work! I thought that I would have to go from Job > JobOpr > JobOpDtl > LaborDtl since it seems like that’s the way the tables are joined and the indexes would help out. Or I thought I read that somewhere. I’ll do some more testing but this might work.

Thanks!