Quick Search SLOWER with criteria

Epicor ERP 10
1

Hi!

I made a BAQ to use in a quick search in Job Status Maintenance.

They asked if I could add some criteria to the quick search so they could filter by Part # and Req By Date.

When I run the quick search wide open, with no criteria, it returns all results (10k+ records) immediately.

When I add in one or both criteria, it takes minutes to bring up less results.

I would expect criteria = less results = faster, but it isn’t behaving that way.

Has anyone else seen this or am I doing something terribly wrong?

2

@hackaphreaka Without knowing anything about the BAQ, adding criteria does slow it down slightly, because the database server has to do more work to look for the specific rows to match the criteria. But if it takes minutes to return data, there may be something up with the BAQ.

3

My 2 criteria fields are JobHead.ReqDueDate and PartMtl.MtlPartNum.

Really the MtlPartNum is the issue. Adding criteria in that field significantly (makes unusable) increases the time to get results.

	[JobHead].[JobNum] as [JobHead_JobNum],
	[JobHead].[PartNum] as [JobHead_PartNum],
	[JobHead].[ProdQty] as [JobHead_ProdQty],
	[JobHead].[ReqDueDate] as [JobHead_ReqDueDate],
	[LaminateParts].[PartMtl_MtlPartNum] as [PartMtl_MtlPartNum],
	(JobHead.ProdQty* LaminateParts.PartMtl_QtyPer) as [Calculated_LaminateReq],
	[LaminateParts].[Calculated_TotalOnHand] as [Calculated_TotalOnHand],
	[LumberParts].[PartMtl1_MtlPartNum] as [PartMtl1_MtlPartNum],
	(JobHead.ProdQty* LumberParts.PartMtl1_QtyPer) as [Calculated_LumberReq],
	[LumberParts].[Calculated_TotalOnHand] as [Calculated_TotalOnHand01],
	[LaminateParts].[PartPlant_ProcessMRP] as [PartPlant_ProcessMRP]
from Erp.JobHead as JobHead
left outer join  (select 
	[PartMtl].[PartNum] as [PartMtl_PartNum],
	[PartMtl].[MtlPartNum] as [PartMtl_MtlPartNum],
	[PartsWithTotalOnHand].[Calculated_TotalOnHand] as [Calculated_TotalOnHand],
	[PartMtl].[QtyPer] as [PartMtl_QtyPer],
	[PartPlant].[ProcessMRP] as [PartPlant_ProcessMRP]
from Erp.PartMtl as PartMtl
inner join Erp.Part as Part1 on 
	PartMtl.Company = Part1.Company
	and PartMtl.MtlPartNum = Part1.PartNum
	and ( Part1.ClassID = '"LAM"'  )

inner join Erp.PartPlant as PartPlant on 
	Part1.Company = PartPlant.Company
	and Part1.PartNum = PartPlant.PartNum
left outer join  (select 
	[Part].[PartNum] as [Part_PartNum],
	((case when sum(PartBin.OnhandQty) is not NULL then sum(PartBin.OnhandQty) else 0 end)) as [Calculated_TotalOnHand]
from Erp.Part as Part
left outer join Erp.PartBin as PartBin on 
	Part.Company = PartBin.Company
	and Part.PartNum = PartBin.PartNum
group by [Part].[PartNum])  as PartsWithTotalOnHand on 
	PartMtl.MtlPartNum = PartsWithTotalOnHand.Part_PartNum)  as LaminateParts on 
	JobHead.PartNum = LaminateParts.PartMtl_PartNum
left outer join  (select 
	[PartMtl1].[PartNum] as [PartMtl1_PartNum],
	[PartMtl1].[MtlPartNum] as [PartMtl1_MtlPartNum],
	[PartsWithTotalOnHand1].[Calculated_TotalOnHand] as [Calculated_TotalOnHand],
	[PartMtl1].[QtyPer] as [PartMtl1_QtyPer]
from Erp.PartMtl as PartMtl1
inner join Erp.Part as Part2 on 
	PartMtl1.Company = Part2.Company
	and PartMtl1.MtlPartNum = Part2.PartNum
	and ( Part2.ClassID = '"MIL"'  )

left outer join  (select 
	[Part].[PartNum] as [Part_PartNum],
	((case when sum(PartBin.OnhandQty) is not NULL then sum(PartBin.OnhandQty) else 0 end)) as [Calculated_TotalOnHand]
from Erp.Part as Part
left outer join Erp.PartBin as PartBin on 
	Part.Company = PartBin.Company
	and Part.PartNum = PartBin.PartNum
group by [Part].[PartNum])  as PartsWithTotalOnHand1 on 
	PartMtl1.MtlPartNum = PartsWithTotalOnHand1.Part_PartNum)  as LumberParts on 
	JobHead.PartNum = LumberParts.PartMtl1_PartNum
inner join Erp.JobOper as JobOper on 
	JobHead.Company = JobOper.Company
	and JobHead.JobNum = JobOper.JobNum
	and ( JobOper.OpCode = '"wrap"'  )

where (JobHead.JobFirm = false)
order by JobHead.ReqDueDate
4

Hi Garret,

Maybe something in this older post will be helpful?

Nancy