Query that runs in 3 seconds in SQL takes LOOONG time in epicor

Epicor ERP 10
1

So I’m trying to figure out what the big difference is. I have a query in Epicor that we have been using for months. Last week it really started bogging down. We can get around the slowness if we use some filters on the dashboard that it’s in, but we shouldn’t have to. I have it in a dashboard and have a filter there to limit the number of records it returns. If I copy and paste the SQL into toad and run the query (wide open with no filtering) it runs in 3 seconds. It’s also locking up other programs I have running unrelated to Epicor (like firefox), but not all programs.

Where can I start looking for problems?

Here’s the SQL

select 
	[JobAsmbl].[JobNum] as [JobAsmbl_JobNum],
	[JobAsmbl].[PartNum] as [JobAsmbl_PartNum],
	[JobAsmbl].[Description] as [JobAsmbl_Description],
	[JobAsmbl].[RequiredQty] as [JobAsmbl_RequiredQty],
	[LaborDtl].[LaborQty] as [LaborDtl_LaborQty],
	[JobOper].[OpComplete] as [JobOper_OpComplete],
	[LaborDtl].[AssemblySeq] as [LaborDtl_AssemblySeq],
	[LaborDtl].[OprSeq] as [LaborDtl_OprSeq],
	[LaborDtl].[ClockInDate] as [LaborDtl_ClockInDate],
	[LaborDtl].[ClockOutTime] as [LaborDtl_ClockOutTime],
	(((select 
	[UD08].[ShortChar02] as [UD08_ShortChar02]
from Ice.UD08 as UD08
where (UD08.ShortChar02 = 'P'  and UD08.Key3 = '0')
 and UD08.Company = JobAsmbl.Company  and UD08.Key1 = JobAsmbl.JobNum  and UD08.Key2 = JobAsmbl.AssemblySeq  and UD08.ShortChar01 = JobAsmbl.PartNum))) as [Calculated_Pack]
from Erp.JobAsmbl as JobAsmbl
inner join Erp.JobOper as JobOper on 
	JobAsmbl.Company = JobOper.Company
	and JobAsmbl.JobNum = JobOper.JobNum
	and JobAsmbl.AssemblySeq = JobOper.AssemblySeq
	and ( JobOper.OpCode = 'PAINT'  )

inner join Erp.LaborDtl as LaborDtl on 
	JobOper.JobNum = LaborDtl.JobNum
	and JobOper.AssemblySeq = LaborDtl.AssemblySeq
	and JobOper.OpCode = LaborDtl.OpCode
	and ( not LaborDtl.LaborQty = 0  )
order by LaborDtl.ClockInDate Desc, LaborDtl.ClockOutTime Desc
2

So I added a company = company on the JobOp and LaborDtl tables. Now it runs in about a second.

3

Yes, you should always link up the entire index, which includes the company columns. This is working as expected.

4

Is there documentation there that says what the entire index is? I know that company should be joined with company, but are there others that are specific to tables?

5

Check out DataSet Relationships:

image
image.png885×299 13.9 KB

6

also see Data Dictionary Viewer

image
image.png976×541 42.3 KB

7

oh, look at that menu hiding in all of those folders. No wonder I never noticed it.

image

8

There’s an article on parameter sniffing that I still need to dive into regarding differences in query execution times between SSMS and applications: Slow in the Application, Fast in SSMS?

A long read, but I’ve come across multiple references to it, so if your stuff is still slow, it might be worth a go.

Ok, now I feel incredibly lazy.