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

Epicor ERP 10
1

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!

2

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.

3

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.

4

No case issues with your PlantIDs? MfgSys vs Mfgsys?

5

Nope - all consistent.

1 Like
6

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.

7

In Epicor what does the execution plan say?

8

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!

9

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.

2019-07-30_15-43-58
2019-07-30_15-43-58.jpg894Ɨ567 149 KB

1 Like
10

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
11

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

12

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

Pierre

3 Likes
13

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?

14

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

15

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

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

1 Like
16

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
17

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ā€™.

2 Likes