Current and Prior Revision BAQ/SQL Performance

Epicor ERP 10
1

I am writing a query to return the current revision and the prior revision in two different columns. When I write it in the BAQ designer it takes 15 seconds to return 3 parts. When I paste the code in SQL Management Studio it runs instantly. Any thoughts on what I may be doing wrong or how to improve the BAQ performance?

BAQ Output:

image
image.png902×145 6.01 KB

Top query:

image
image.png716×472 17.7 KB

The sub query is basically generating a rownumber and sorting by effective date descending and the top query is filtering the subquery by row two for the prior revision.

Here is the code the BAQ generated:
select
[Part].[PartNum] as [Part_PartNum],
[Part].[PartDescription] as [Part_PartDescription],
[PartRev].[RevisionNum] as [PartRev_RevisionNum],
[PartRev].[Approved] as [PartRev_Approved],
[PartRev].[EffectiveDate] as [PartRev_EffectiveDate],
[LastRevision].[PartRevLastRevision_RevisionNum] as [PartRevLastRevision_RevisionNum],
[LastRevision].[PartRevLastRevision_EffectiveDate] as [PartRevLastRevision_EffectiveDate],
((case when LastRevision.PartRevLastRevision_RevisionNum <> ‘’ then LastRevision.PartRevLastRevision_RevisionNum else ‘New Part’ end)) as [Calculated_PriorRevision],
[PartRev].[ECO] as [PartRev_ECO]
from Erp.Part as Part
inner join Erp.PartRev as PartRev on
Part.Company = PartRev.Company
and Part.PartNum = PartRev.PartNum
and ( PartRev.ECO = ‘152’ and PartRev.PartNum = ANY (select PartRevLastRevision_PartNum from ((select
[PartRevLastRevision].[PartNum] as [PartRevLastRevision_PartNum],
[PartRevLastRevision].[RevisionNum] as [PartRevLastRevision_RevisionNum],
[PartRevLastRevision].[Approved] as [PartRevLastRevision_Approved],
[PartRevLastRevision].[EffectiveDate] as [PartRevLastRevision_EffectiveDate],
[PartRevLastRevision].[Company] as [PartRevLastRevision_Company],
(ROW_NUMBER() OVER(PARTITION BY PartRevLastRevision.PartNum ORDER BY PartRevLastRevision.EffectiveDate DESC)) as [Calculated_RevisionRow]
from Erp.PartRev as PartRevLastRevision)) as LastRevision) )

left outer join (select
[PartRevLastRevision].[PartNum] as [PartRevLastRevision_PartNum],
[PartRevLastRevision].[RevisionNum] as [PartRevLastRevision_RevisionNum],
[PartRevLastRevision].[Approved] as [PartRevLastRevision_Approved],
[PartRevLastRevision].[EffectiveDate] as [PartRevLastRevision_EffectiveDate],
[PartRevLastRevision].[Company] as [PartRevLastRevision_Company],
(ROW_NUMBER() OVER(PARTITION BY PartRevLastRevision.PartNum ORDER BY PartRevLastRevision.EffectiveDate DESC)) as [Calculated_RevisionRow]
from Erp.PartRev as PartRevLastRevision) as LastRevision on
PartRev.Company = LastRevision.PartRevLastRevision_Company
and PartRev.PartNum = LastRevision.PartRevLastRevision_PartNum
and ( LastRevision.Calculated_RevisionRow = 2 )

Thanks,
Ross

2

I looked at this again and realized that I pasted in an experiment that didn’t make a different in the subquery. Here is the updated code from the BAQ designer and the performance is the same.

select

           [Part].[PartNum] as [Part_PartNum],

           [Part].[PartDescription] as [Part_PartDescription],

           [PartRev].[RevisionNum] as [PartRev_RevisionNum],

           [PartRev].[Approved] as [PartRev_Approved],

           [PartRev].[EffectiveDate] as [PartRev_EffectiveDate],

           [LastRevision].[PartRevLastRevision_RevisionNum] as [PartRevLastRevision_RevisionNum],

           [LastRevision].[PartRevLastRevision_EffectiveDate] as [PartRevLastRevision_EffectiveDate],

           ((case when LastRevision.PartRevLastRevision_RevisionNum <> '' then LastRevision.PartRevLastRevision_RevisionNum else 'New Part' end)) as [Calculated_PriorRevision],

           [PartRev].[ECO] as [PartRev_ECO]

from Erp.Part as Part

inner join Erp.PartRev as PartRev on

           Part.Company = PartRev.Company

           and Part.PartNum = PartRev.PartNum

           and ( PartRev.ECO = '152'  )

left outer join (select

           [PartRevLastRevision].[PartNum] as [PartRevLastRevision_PartNum],

           [PartRevLastRevision].[RevisionNum] as [PartRevLastRevision_RevisionNum],

           [PartRevLastRevision].[Approved] as [PartRevLastRevision_Approved],

           [PartRevLastRevision].[EffectiveDate] as [PartRevLastRevision_EffectiveDate],

           [PartRevLastRevision].[Company] as [PartRevLastRevision_Company],

           (ROW_NUMBER() OVER(PARTITION BY PartRevLastRevision.PartNum ORDER BY PartRevLastRevision.EffectiveDate DESC)) as [Calculated_RevisionRow]

from Erp.PartRev as PartRevLastRevision) as LastRevision on

           PartRev.Company = LastRevision.PartRevLastRevision_Company

           and PartRev.PartNum = LastRevision.PartRevLastRevision_PartNum

           and ( LastRevision.Calculated_RevisionRow = 2  )

Thanks,

Ross

3

Hello Ross,
I have found that if I add a criteria on the first table as in your case Part.Company = BAQ Current_Company, my queries run much faster!

try it.

Pierre

2 Likes
4

Thanks, Pierre.

I have sort of ‘grew up’ doing that in Epicor in V8 and E9 and don’t know why I missed that here.

Anyway, I added that in this example and it still runs at 15+ seconds in the BAQ designer.

Thanks for the idea!

Ross

5

Your solution and advice worked for my Complex BAQ query. Thank you. Very helpful and useful post. I used the BAQ CurrentCompanyConstant criteria in three tables link to a subquery that was filtering rows. The BAQ is used in a BAQ DataView. Oddly, the BAQ performance from the designer was very acceptable but not from the Epicor Form for all queries tested. Your advice made it work under all query conditions.