BAQ Timeout - SSMS runs same query in < 1sec`

Hi

Anybody able to give me some pointers on this one please - this BAQ in E10.2.300 never runs, it times out. BUT, take the same SQL code generated by the BAQ and run it in SSMS and it runs in less than a second, and returns 214 rows.

select 
	[Part].[PartNum] as [Part_PartNum],
	[Part].[PartDescription] as [Part_PartDescription],
	[PartPlant].[Number01] as [PartPlant_Number01]
from Erp.Part as Part
inner join Erp.PartPlant as PartPlant on 
	Part.Company = PartPlant.Company
	and Part.PartNum = PartPlant.PartNum
where (Part.PartNum in (select OrderDtl_PartNum from (select distinct
	[OrderDtl].[PartNum] as [OrderDtl_PartNum]
from Erp.OrderDtl as OrderDtl
where (OrderDtl.Company = 'BVD'  and OrderDtl.RequestDate > @StartDate  and OrderDtl.RequestDate < @EndDate)) as OrderDtl))
order by Part.PartNum

Thanks!

If you re-write the query one portion at a time, does it work until a certain point?
I had this happen in an environment and support was never able to help diagnose the root cause.

No just tried that - the subquery works on its own, produces 214 records.

When I add in a second query, make that the top level and the original subquery InnerJoin then it just doesn’t run and timeouts out.

No case issues with your PlantIDs? MfgSys vs Mfgsys?

Nope - all consistent.

1 Like

On it’s own, the distinct subquery runs quick.

This also runs very quick, < 1 sec (but is obviously only returning 2 records)

select 
	[Part].[PartNum] as [Part_PartNum],
	[Part].[PartDescription] as [Part_PartDescription],
	[PartPlant].[Number01] as [PartPlant_Number01]
from Erp.Part as Part
inner join Erp.PartPlant as PartPlant on 
	Part.Company = PartPlant.Company
	and Part.PartNum = PartPlant.PartNum
where (Part.PartNum in ('ACA005KA', 'NYC005KZ'))

For whatever reason Epicor doesn’t like the where clause, but SSMS laps it up.

In Epicor what does the execution plan say?

The 2 executions plans are slightly different. They both suggest a missing Index, as follows:

E10 - wants OrderDtl with Company, PartNum, RequestDate

SSMS - wants OrderDtl with Company, RequestDate (and INCLUDES PartNum)

Does that help, or is there something else I can see on the query plans? I’m not considering adding any index, because even though SSMS suggests it the query runs in < 1 sec anyway!

The query displayed in the BAQ screen is not what is ran in reality. Add a filter on Part.Company in the top query and it should help fixing your issue.

See the disclaimer added in 10.2.400.

1 Like

Oh wow - not see that in 10.2.400, BUT you were totally right.

Adding that Part.Company filter in the top level BAQ solved it. Now runs in < 1 sec as per SSMS.

What gives? What is the explanation for that working? Thanks!

2 Likes

Comparing the execution plan before and after the change would answer your question.

I usually add Company = BAQ CurrentCompany Constant in my first table and it changed all my responsiveness of my queries…

Pierre

3 Likes

Indeed - the suggested missing index is now the same.

General question on execution plans then - I normally just look for the missing index bit, is there anything else that you “read” into the plan result?

Sure, many things. By example, you search for indexes scan, which kills performance. But we can’t cover everything on a post hehe.

So basically a seek is good, scan bad right? :grinning:

Leave it there for this post, thanks everybody for quick replies!

1 Like

Can this go as an enhancement to BAQ execution?
Seem odd, but I’ve had a few BAQs that o have to add an innocuous filter to speed execution but orders of magnitude.

1 Like

Just created a BAQ that had a criteria of Company = CurrentCompany. Took 350ms to run, set the criteria to Company= ‘TT’ and it ran in just over 50! What the…time for a bit of a trace I guess. Can’t understand how CurrentCompany isnt’t the same as ‘TT’.

1 Like