Make this BAQ More Efficient

[NateS (Nate Schoonover)]

Hi all!
I am not sure how you all will respond to this, so let me throw it out there.

/*
 * Disclaimer!!!
 * This is not a real query being executed, but a simplified version for general vision.
 * Executing it with any other tool may produce a different result.
 */
 
select distinct
	[SubQuery5].[OrderHed_OrderNum] as [OrderHed_OrderNum],
	[SubQuery5].[OrderHed_PONum] as [OrderHed_PONum],
	(iif(right(SubQuery6.UD03_sq6_Key3,3)='---', left(right(SubQuery6.UD03_sq6_Key3,6),3) + '-' + SubQuery6.UD03_sq6_ShortChar01, left(right(SubQuery6.UD03_sq6_Key3,7),3) + right(SubQuery6.UD03_sq6_Key3,3) + '-' + SubQuery6.UD03_sq6_ShortChar01)) as [Calculated_MyXRev],
	[SubQuery5].[Calculated_OurXRev] as [Calculated_OurXRev],
	[SubQuery5].[OrderDtl_PartNum] as [OrderDtl_PartNum],
	[SubQuery5].[OrderDtl_OrderNum] as [OrderDtl_OrderNum],
	[SubQuery5].[OrderDtl_OrderLine] as [OrderDtl_OrderLine],
	[SubQuery5].[Calculated_FirstRel] as [Calculated_FirstRel],
	[SubQuery5].[OrderDtl_UnitPrice] as [OrderDtl_UnitPrice],
	[SubQuery5].[OrderRel_ReqDate] as [OrderRel_ReqDate],
	[SubQuery6].[Calculated_MinDate] as [Calculated_MinDate],
	[SubQuery5].[Calculated_Shipped] as [Calculated_Shipped],
	[SubQuery5].[Calculated_OurRemainQty] as [Calculated_OurRemainQty],
	(SubQuery6.UD03_sq6_Number01 -SubQuery6.UD03_sq6_Number03) as [Calculated_CustFinalQty],
	(convert(varchar,SubQuery5.OrderHed_OrderNum) + '-' + convert(varchar,SubQuery5.OrderDtl_OrderLine)  + '-' +convert(varchar, SubQuery5.Calculated_FirstRel)) as [Calculated_OrderLnRel],
	(0) as [Calculated_CloseRelease],
	(0) as [Calculated_UseCustDate],
	(iif(convert(varchar, SubQuery6.Calculated_MinDate , 101) is null, iif((SubQuery5.Calculated_Shipped >= 1 or (SubQuery10.Calculated_FirstJob) != '' ), 'Close Me', 'Delete Me'), 'Use Date')) as [Calculated_Suggestion],
	[SubQuery10].[Calculated_FirstJob] as [Calculated_FirstJob]
from  (select 
	[SubQuery4].[OrderHed_Company] as [OrderHed_Company],
	[SubQuery4].[OrderHed_OrderNum] as [OrderHed_OrderNum],
	[SubQuery4].[OrderHed_PONum] as [OrderHed_PONum],
	[SubQuery4].[Calculated_OurXRev] as [Calculated_OurXRev],
	[SubQuery4].[OrderDtl_PartNum] as [OrderDtl_PartNum],
	[SubQuery4].[OrderDtl_OrderNum] as [OrderDtl_OrderNum],
	[SubQuery4].[OrderDtl_OrderLine] as [OrderDtl_OrderLine],
	[SubQuery4].[OrderDtl_UnitPrice] as [OrderDtl_UnitPrice],
	[SubQuery4].[OrderDtl_RevisionNum] as [OrderDtl_RevisionNum],
	[SubQuery4].[OrderRel_ReqDate] as [OrderRel_ReqDate],
	[SubQuery4].[Calculated_OurRemainQty] as [Calculated_OurRemainQty],
	[SubQuery4].[OrderRel_SellingReqQty] as [OrderRel_SellingReqQty],
	[SubQuery4].[OrderRel_OurStockShippedQty] as [OrderRel_OurStockShippedQty],
	(iif(right(UD03.Key3,3)='---', left(right(UD03.Key3,6),3) + '-' + UD03.ShortChar01, left(right(UD03.Key3,7),3) + right(UD03.Key3,3) + '-' + UD03.ShortChar01)) as [Calculated_MyXRev],
	(UD03.Number01-UD03.Number03) as [Calculated_CustFinalQty],
	[SubQuery4].[Calculated_Shipped] as [Calculated_Shipped],
	(min( SubQuery4.OrderRel_OrderRelNum)) as [Calculated_FirstRel]
from  (select 
	[OrderHed].[Company] as [OrderHed_Company],
	[OrderHed].[PONum] as [OrderHed_PONum],
	[OrderDtl].[PartNum] as [OrderDtl_PartNum],
	[OrderDtl].[OrderNum] as [OrderDtl_OrderNum],
	[OrderDtl].[OrderLine] as [OrderDtl_OrderLine],
	[OrderDtl].[RevisionNum] as [OrderDtl_RevisionNum],
	(replace(OrderDtl.XRevisionNum,' ','' )) as [Calculated_OurXRev],
	[OrderRel].[OrderRelNum] as [OrderRel_OrderRelNum],
	[OrderRel].[OurReqQty] as [OrderRel_OurReqQty],
	[OrderRel].[ReqDate] as [OrderRel_ReqDate],
	[OrderDtl].[UnitPrice] as [OrderDtl_UnitPrice],
	[OrderRel].[SellingReqQty] as [OrderRel_SellingReqQty],
	[OrderRel].[OurStockShippedQty] as [OrderRel_OurStockShippedQty],
	(OrderRel.SellingReqQty - OrderRel.OurStockShippedQty - OrderRel.OurJobShippedQty) as [Calculated_OurRemainQty],
	[OrderHed].[CustNum] as [OrderHed_CustNum],
	[OrderHed].[OpenOrder] as [OrderHed_OpenOrder],
	[OrderDtl].[OpenLine] as [OrderDtl_OpenLine],
	[OrderRel].[OpenRelease] as [OrderRel_OpenRelease],
	[OrderHed].[OrderNum] as [OrderHed_OrderNum],
	(OrderRel.OurStockShippedQty + OrderRel.OurJobShippedQty) as [Calculated_Shipped]
from Erp.OrderHed as OrderHed
inner join Erp.OrderDtl as OrderDtl on 
	OrderHed.Company = OrderDtl.Company
	and OrderHed.OrderNum = OrderDtl.OrderNum
inner join Erp.OrderRel as OrderRel on 
	OrderDtl.Company = OrderRel.Company
	and OrderDtl.OrderNum = OrderRel.OrderNum
	and OrderDtl.OrderLine = OrderRel.OrderLine
	and ( OrderRel.OpenRelease = true  )

where (OrderHed.CustNum = 1))  as SubQuery4
left outer join Ice.UD03 as UD03 on 
	SubQuery4.OrderHed_Company = UD03.Company
	and SubQuery4.OrderHed_PONum = UD03.Key2
	and SubQuery4.Calculated_OurXRev = MyXRev
	and SubQuery4.OrderDtl_PartNum = UD03.Key4
	and SubQuery4.OrderDtl_UnitPrice = UD03.Number02
	and SubQuery4.OrderRel_ReqDate = UD03.Date01
	and (SubQuery4.Calculated_OurRemainQty = CustFinalQty
	or SubQuery4.OrderRel_SellingReqQty = UD03.Number01)
group by [SubQuery4].[OrderHed_Company],
	[SubQuery4].[OrderHed_OrderNum],
	[SubQuery4].[OrderHed_PONum],
	[SubQuery4].[Calculated_OurXRev],
	[SubQuery4].[OrderDtl_PartNum],
	[SubQuery4].[OrderDtl_OrderNum],
	[SubQuery4].[OrderDtl_OrderLine],
	[SubQuery4].[OrderDtl_UnitPrice],
	[SubQuery4].[OrderDtl_RevisionNum],
	[SubQuery4].[OrderRel_ReqDate],
	[SubQuery4].[Calculated_OurRemainQty],
	[SubQuery4].[OrderRel_SellingReqQty],
	[SubQuery4].[OrderRel_OurStockShippedQty],
	(iif(right(UD03.Key3,3)='---', left(right(UD03.Key3,6),3) + '-' + UD03.ShortChar01, left(right(UD03.Key3,7),3) + right(UD03.Key3,3) + '-' + UD03.ShortChar01)),
	(UD03.Number01-UD03.Number03),
	[SubQuery4].[Calculated_Shipped])  as SubQuery5
left outer join  (select distinct
	[UD03_sq6].[Company] as [UD03_sq6_Company],
	(iif(right(UD03_sq6.Key3,3)='---', left(right(UD03_sq6.Key3,6),3) + '-' + UD03_sq6.ShortChar01, left(right(UD03_sq6.Key3,7),3) + right(UD03_sq6.Key3,3) + '-' + UD03_sq6.ShortChar01)) as [Calculated_MyXRev],
	(Number01-Number03) as [Calculated_CustFinalQty],
	(min(UD03_sq6.Date01)) as [Calculated_MinDate],
	[UD03_sq6].[Key2] as [UD03_sq6_Key2],
	[UD03_sq6].[Key4] as [UD03_sq6_Key4],
	[UD03_sq6].[Number01] as [UD03_sq6_Number01],
	[UD03_sq6].[Number02] as [UD03_sq6_Number02],
	[UD03_sq6].[Key3] as [UD03_sq6_Key3],
	[UD03_sq6].[ShortChar01] as [UD03_sq6_ShortChar01],
	[UD03_sq6].[Number03] as [UD03_sq6_Number03]
from  (select 
	[OrderHed1].[Company] as [OrderHed1_Company],
	[OrderHed1].[PONum] as [OrderHed1_PONum],
	[OrderDtl1].[PartNum] as [OrderDtl1_PartNum],
	[OrderDtl1].[OrderNum] as [OrderDtl1_OrderNum],
	[OrderDtl1].[OrderLine] as [OrderDtl1_OrderLine],
	(replace(OrderDtl1.XRevisionNum,' ','')) as [Calculated_OurXRev],
	[OrderRel1].[OrderRelNum] as [OrderRel1_OrderRelNum],
	[OrderRel1].[OurReqQty] as [OrderRel1_OurReqQty],
	[OrderRel1].[ReqDate] as [OrderRel1_ReqDate],
	[OrderDtl1].[UnitPrice] as [OrderDtl1_UnitPrice],
	[OrderRel1].[SellingReqQty] as [OrderRel1_SellingReqQty],
	[OrderRel1].[OurStockShippedQty] as [OrderRel1_OurStockShippedQty],
	(OrderRel1.SellingReqQty - OrderRel1.OurStockShippedQty) as [Calculated_OurRemainQty]
from Erp.OrderHed as OrderHed1
inner join Erp.OrderDtl as OrderDtl1 on 
	OrderHed1.Company = OrderDtl1.Company
	and OrderHed1.OrderNum = OrderDtl1.OrderNum
inner join Erp.OrderRel as OrderRel1 on 
	OrderDtl1.Company = OrderRel1.Company
	and OrderDtl1.OrderNum = OrderRel1.OrderNum
	and OrderDtl1.OrderLine = OrderRel1.OrderLine)  as SubQuery7
right outer join Ice.UD03 as UD03_sq6 on 
	UD03_sq6.Company = SubQuery7.OrderHed1_Company
	and UD03_sq6.Key2 = SubQuery7.OrderHed1_PONum
	and MyXRev = SubQuery7.Calculated_OurXRev
	and UD03_sq6.Key4 = SubQuery7.OrderDtl1_PartNum
	and UD03_sq6.Number02 = SubQuery7.OrderDtl1_UnitPrice
	and UD03_sq6.Date01 = SubQuery7.OrderRel1_ReqDate
	and (CustFinalQty = SubQuery7.Calculated_OurRemainQty
	or UD03_sq6.Number01 = SubQuery7.OrderRel1_SellingReqQty)
left outer join  (select 
	[OrderHed3].[Company] as [OrderHed3_Company],
	[OrderHed3].[PONum] as [OrderHed3_PONum],
	[OrderHed3].[OrderNum] as [OrderHed3_OrderNum],
	[OrderDtl3].[OrderLine] as [OrderDtl3_OrderLine],
	[OrderDtl3].[PartNum] as [OrderDtl3_PartNum],
	[OrderDtl3].[UnitPrice] as [OrderDtl3_UnitPrice],
	(replace(OrderDtl3.XRevisionNum, ' ', '')) as [Calculated_OurXRev]
from Erp.OrderHed as OrderHed3
inner join Erp.OrderDtl as OrderDtl3 on 
	OrderHed3.Company = OrderDtl3.Company
	and OrderHed3.OrderNum = OrderDtl3.OrderNum)  as SubQuery9 on 
	UD03_sq6.Company = SubQuery9.OrderHed3_Company
	and UD03_sq6.Key2 = SubQuery9.OrderHed3_PONum
	and MyXRev = SubQuery9.Calculated_OurXRev
	and UD03_sq6.Key4 = SubQuery9.OrderDtl3_PartNum
	and UD03_sq6.Number02 = SubQuery9.OrderDtl3_UnitPrice
where (SubQuery7.OrderHed1_PONum is null and SubQuery9.OrderHed3_PONum is not null)

group by [UD03_sq6].[Company],
	(iif(right(UD03_sq6.Key3,3)='---', left(right(UD03_sq6.Key3,6),3) + '-' + UD03_sq6.ShortChar01, left(right(UD03_sq6.Key3,7),3) + right(UD03_sq6.Key3,3) + '-' + UD03_sq6.ShortChar01)),
	(Number01-Number03),
	[UD03_sq6].[Key2],
	[UD03_sq6].[Key4],
	[UD03_sq6].[Number01],
	[UD03_sq6].[Number02],
	[UD03_sq6].[Key3],
	[UD03_sq6].[ShortChar01],
	[UD03_sq6].[Number03])  as SubQuery6 on 
	SubQuery5.OrderHed_Company = SubQuery6.UD03_sq6_Company
	and SubQuery5.OrderHed_PONum = SubQuery6.UD03_sq6_Key2
	and SubQuery5.Calculated_OurXRev = SubQuery6.Calculated_MyXRev
	and SubQuery5.OrderDtl_PartNum = SubQuery6.UD03_sq6_Key4
	and SubQuery5.OrderDtl_UnitPrice = SubQuery6.UD03_sq6_Number02
	and (SubQuery5.Calculated_OurRemainQty = SubQuery6.Calculated_CustFinalQty
	or SubQuery5.OrderRel_SellingReqQty = SubQuery6.UD03_sq6_Number01)
left outer join  (select 
	[JobHead].[Company] as [JobHead_Company],
	[JobHead].[PartNum] as [JobHead_PartNum],
	[JobHead].[RevisionNum] as [JobHead_RevisionNum],
	(min(JobHead.JobNum)) as [Calculated_FirstJob]
from Erp.JobHead as JobHead
where (JobHead.JobClosed = false)
group by [JobHead].[Company],
	[JobHead].[PartNum],
	[JobHead].[RevisionNum])  as SubQuery10 on 
	SubQuery5.OrderHed_Company = SubQuery10.JobHead_Company
	and SubQuery5.OrderDtl_PartNum = SubQuery10.JobHead_PartNum
	and SubQuery5.OrderDtl_RevisionNum = SubQuery10.JobHead_RevisionNum
order by SubQuery5.OrderDtl_PartNum, SubQuery5.OrderDtl_OrderNum, SubQuery5.OrderDtl_OrderLine, SubQuery5.Calculated_FirstRel

This monstrosity of an updatable BAQ is one of the ways we deal with a customer file containing order information. The overarching goal of this particular BAQ is to compare the customer’s requested orders (held in UD03), with the order/releases we have open in Epicor (from OrderHed,Dtl,Rel). The resulting list should show our open releases that can not be matched to the customer’s requested orders based on a host of criteria. Along with this, the BAQ calculates a suggested action for the release. Either close the release, or, if the PO, Line (Xrev), Part, and Quantity match, then we can just change the date on the release to a date that the customer requests.

With the data I have now, this BAQ returns about 2100 rows in 5.4 to 6.7 seconds. I would like to get that down to a second or less if possible. I have done a few things to improve the performance of this BAQ. First, I made sure to include Company as the first join in all joins. I also moved most of my join fields in UD03 into key fields to utilize indexing. I looked into changing the outer joins for inner joins, but that is where I got stuck. I am also joining on a few calculated fields. Could that also impact performance?

Without completely doing my job for me, can you make any suggestions based on the SQL and requirements I gave? How would you go about replacing outer joins with inner? Would that even help my efficiency?

Thanks for your time!
Nate

See the related topic below for more info:

Speed up BAQ refresh in Dashboard

Hello all you big brains out there! Today I have a very broad question. Without delving into specific use cases, are there any obvious ways to speed up the refreshing of BAQs in a Dashboard? I did find a useful tip that I will be utilizing in my BPMs: Has anyone else adopted any little tricks like this for speeding up your BAQs? Thanks for your time! Nate

[josecgomez]

Can you post the actual BAQ (export)

[bradberkobien]

Honestly interested to see the logic in BAQ’s to be able to create that SQL output. It’s cool to have BAQ’s, I feel it’s awesome to have non-developer’s be able to understand the logic without having to code. I do think that there is a downside (as mentioned in the post) that sometimes it’s not always optimal.

[NateS (Nate Schoonover)]

CustUnmatchedOrders_Sample.baq (215.7 KB)

[NateS (Nate Schoonover)]

So did anyone open this monster of a BAQ and look at the joins? Any suggestions on speeding things up a bit?

[Dmitry_Kashulin]

You use DISTINCT modificator for top level subquery. This instruction may cause performance degradation on big amounts or rows and columns. Consider adding a join or a condition to get rid of duplicated records in result.
BAQ Designer offers an useful action - Get Query Execution Plan.
If your DB admin is not extremely mad about security you may be able to receive query execution plan and analyze it in MS SSMS for any bottlenecks in query definition.

[John_Mitchell]

@NateS One thing that I have found to help speed up BAQ’s is to only use the minimal number of joins and instead to try to put the logic in the filters.

For example: instead of joining on Company and OrderNum only join on OrderNum and filter the query’s by BAQ CurrComp Constant. The more you can filter the tables the less data MSSQL has to go through.

Second, make sure to only ever join indexed fields. If you have to join on a custom field then you will need to add an index otherwise MSSQL has to do a full table scan before it can even do the filter.

I captured your BAQ in SQL Profiler and here is the query that MSSQL is actually executing. You can change the company ID in the company, user, and current company and AvailCompLst variables. You can then run it yourself to see how bad it looks.

SET STATISTICS IO ON
declare @p3 Ice.QueryParamTableType
insert into @p3 values(N'01')

exec sp_executesql N'
declare @AvailCompLst table(Company nchar(8) primary key);
insert into @AvailCompLst values(N'''');
insert into @AvailCompLst(Company) select ParamValue from @_compList; 
--baqd08ffa2af-0542-430e-a1e7-a4d269c9fd8b;
SET DATEFORMAT mdy;

select distinct [SubQuery5].[OrderHed_OrderNum] [OrderHed_OrderNum],[SubQuery5].[OrderHed_PONum] [OrderHed_PONum],iif(right([SubQuery6].[UD03_sq6_Key3],3)=''---'', left(right([SubQuery6].[UD03_sq6_Key3],6),3) + ''-'' + [SubQuery6].[UD03_sq6_ShortChar01], left(right([SubQuery6].[UD03_sq6_Key3],7),3) + right([SubQuery6].[UD03_sq6_Key3],3) + ''-'' + [SubQuery6].[UD03_sq6_ShortChar01]) [Calculated_MyXRev],[SubQuery5].[Calculated_OurXRev] [Calculated_OurXRev],[SubQuery5].[OrderDtl_PartNum] [OrderDtl_PartNum],[SubQuery5].[OrderDtl_OrderNum] [OrderDtl_OrderNum],[SubQuery5].[OrderDtl_OrderLine] [OrderDtl_OrderLine],[SubQuery5].[Calculated_FirstRel] [Calculated_FirstRel],[SubQuery5].[OrderDtl_UnitPrice] [OrderDtl_UnitPrice],[SubQuery5].[OrderRel_ReqDate] [OrderRel_ReqDate],[SubQuery6].[Calculated_MinDate] [Calculated_MinDate],[SubQuery5].[Calculated_Shipped] [Calculated_Shipped],[SubQuery5].[Calculated_OurRemainQty] [Calculated_OurRemainQty],convert(varchar,[SubQuery5].[OrderHed_OrderNum]) + ''-'' + convert(varchar,[SubQuery5].[OrderDtl_OrderLine])  + ''-'' +convert(varchar, [SubQuery5].[Calculated_FirstRel]) [Calculated_OrderLnRel],CAST((0) as BIT) [Calculated_CloseRelease],iif(convert(varchar, [SubQuery6].[Calculated_MinDate] , 101) is null, iif(([SubQuery5].[Calculated_Shipped] >= 1 or ([SubQuery10].[Calculated_FirstJob]) != '''' ), ''Close Me'', ''Delete Me''), ''Use Date'') [Calculated_Suggestion],[SubQuery10].[Calculated_FirstJob] [Calculated_FirstJob],[SubQuery6].[UD03_sq6_Number01] -[SubQuery6].[UD03_sq6_Number03] [Calculated_CustFinalQty],CAST((0) as BIT) [Calculated_UseCustDate]
from (select [SubQuery4].[OrderHed_Company] [OrderHed_Company],[SubQuery4].[OrderHed_OrderNum] [OrderHed_OrderNum],[SubQuery4].[OrderHed_PONum] [OrderHed_PONum],[SubQuery4].[Calculated_OurXRev] [Calculated_OurXRev],[SubQuery4].[OrderDtl_PartNum] [OrderDtl_PartNum],[SubQuery4].[OrderDtl_OrderNum] [OrderDtl_OrderNum],[SubQuery4].[OrderDtl_OrderLine] [OrderDtl_OrderLine],[SubQuery4].[OrderDtl_UnitPrice] [OrderDtl_UnitPrice],[SubQuery4].[OrderDtl_RevisionNum] [OrderDtl_RevisionNum],[SubQuery4].[OrderRel_ReqDate] [OrderRel_ReqDate],[SubQuery4].[Calculated_OurRemainQty] [Calculated_OurRemainQty],[SubQuery4].[OrderRel_SellingReqQty] [OrderRel_SellingReqQty],[SubQuery4].[OrderRel_OurStockShippedQty] [OrderRel_OurStockShippedQty],iif(right([UD03].[Key3],3)=''---'', left(right([UD03].[Key3],6),3) + ''-'' + [UD03].[ShortChar01], left(right([UD03].[Key3],7),3) + right([UD03].[Key3],3) + ''-'' + [UD03].[ShortChar01]) [Calculated_MyXRev],[SubQuery4].[Calculated_Shipped] [Calculated_Shipped],min( [SubQuery4].[OrderRel_OrderRelNum]) [Calculated_FirstRel],[UD03].[Number01]-[UD03].[Number03] [Calculated_CusFinalQty]
from (select [OrderHed].[Company] [OrderHed_Company],[OrderHed].[PONum] [OrderHed_PONum],[OrderDtl].[PartNum] [OrderDtl_PartNum],[OrderDtl].[OrderNum] [OrderDtl_OrderNum],[OrderDtl].[OrderLine] [OrderDtl_OrderLine],[OrderDtl].[RevisionNum] [OrderDtl_RevisionNum],replace([OrderDtl].[XRevisionNum],'' '','''' ) [Calculated_OurXRev],[OrderRel].[OrderRelNum] [OrderRel_OrderRelNum],[OrderRel].[OurReqQty] [OrderRel_OurReqQty],[OrderRel].[ReqDate] [OrderRel_ReqDate],[OrderDtl].[UnitPrice] [OrderDtl_UnitPrice],[OrderRel].[SellingReqQty] [OrderRel_SellingReqQty],[OrderRel].[OurStockShippedQty] [OrderRel_OurStockShippedQty],[OrderRel].[SellingReqQty] - [OrderRel].[OurStockShippedQty] - [OrderRel].[OurJobShippedQty] [Calculated_OurRemainQty],[OrderHed].[CustNum] [OrderHed_CustNum],[OrderHed].[OpenOrder] [OrderHed_OpenOrder],[OrderDtl].[OpenLine] [OrderDtl_OpenLine],[OrderRel].[OpenRelease] [OrderRel_OpenRelease],[OrderHed].[OrderNum] [OrderHed_OrderNum],[OrderRel].[OurStockShippedQty] + [OrderRel].[OurJobShippedQty] [Calculated_Shipped]
from ( [Erp].[OrderHed] inner join @AvailCompLst [AvailCLst_OrderHed] on [Erp].[OrderHed].[Company] is null  Or [Erp].[OrderHed].[Company] = [AvailCLst_OrderHed].[Company])
inner join [Erp].[OrderDtl] on [Erp].[OrderHed].[Company] = [Erp].[OrderDtl].[Company] And [Erp].[OrderHed].[OrderNum] = [Erp].[OrderDtl].[OrderNum]
inner join [Erp].[OrderRel] on ([Erp].[OrderDtl].[Company] = [Erp].[OrderRel].[Company] And [Erp].[OrderDtl].[OrderNum] = [Erp].[OrderRel].[OrderNum] And [Erp].[OrderDtl].[OrderLine] = [Erp].[OrderRel].[OrderLine]) and ([OrderRel].[OpenRelease] = 1)
where [OrderHed].[CustNum] = 1 
)  [SubQuery4]
left outer join ( [Ice].[UD03] inner join @AvailCompLst [AvailCLst_UD03] on [Ice].[UD03].[Company] is null  Or [Ice].[UD03].[Company] = [AvailCLst_UD03].[Company]) on [SubQuery4].[OrderHed_Company] = [Ice].[UD03].[Company] And [SubQuery4].[OrderHed_PONum] = [Ice].[UD03].[Key2] And [SubQuery4].[Calculated_OurXRev] = (iif(right([UD03].[Key3],3)=''---'', left(right([UD03].[Key3],6),3) + ''-'' + [UD03].[ShortChar01], left(right([UD03].[Key3],7),3) + right([UD03].[Key3],3) + ''-'' + [UD03].[ShortChar01])) And [SubQuery4].[OrderDtl_PartNum] = [Ice].[UD03].[Key4] And [SubQuery4].[OrderDtl_UnitPrice] = [Ice].[UD03].[Number02] And [SubQuery4].[OrderRel_ReqDate] = [Ice].[UD03].[Date01] And ([SubQuery4].[Calculated_OurRemainQty] = ([UD03].[Number01]-[UD03].[Number03]) Or [SubQuery4].[OrderRel_SellingReqQty] = [Ice].[UD03].[Number01])
group by [SubQuery4].[OrderHed_Company],[SubQuery4].[OrderHed_OrderNum],[SubQuery4].[OrderHed_PONum],[SubQuery4].[Calculated_OurXRev],[SubQuery4].[OrderDtl_PartNum],[SubQuery4].[OrderDtl_OrderNum],[SubQuery4].[OrderDtl_OrderLine],[SubQuery4].[OrderDtl_UnitPrice],[SubQuery4].[OrderDtl_RevisionNum],[SubQuery4].[OrderRel_ReqDate],[SubQuery4].[Calculated_OurRemainQty],[SubQuery4].[OrderRel_SellingReqQty],[SubQuery4].[OrderRel_OurStockShippedQty],iif(right([UD03].[Key3],3)=''---'', left(right([UD03].[Key3],6),3) + ''-'' + [UD03].[ShortChar01], left(right([UD03].[Key3],7),3) + right([UD03].[Key3],3) + ''-'' + [UD03].[ShortChar01]),[SubQuery4].[Calculated_Shipped],[UD03].[Number01]-[UD03].[Number03]
)  [SubQuery5]
left outer join (select distinct [UD03_sq6].[Company] [UD03_sq6_Company],iif(right([UD03_sq6].[Key3],3)=''---'', left(right([UD03_sq6].[Key3],6),3) + ''-'' + [UD03_sq6].[ShortChar01], left(right([UD03_sq6].[Key3],7),3) + right([UD03_sq6].[Key3],3) + ''-'' + [UD03_sq6].[ShortChar01]) [Calculated_MyXRev],min([UD03_sq6].[Date01]) [Calculated_MinDate],[UD03_sq6].[Key2] [UD03_sq6_Key2],[UD03_sq6].[Key4] [UD03_sq6_Key4],[UD03_sq6].[Number01] [UD03_sq6_Number01],[UD03_sq6].[Number02] [UD03_sq6_Number02],[UD03_sq6].[Key3] [UD03_sq6_Key3],[UD03_sq6].[ShortChar01] [UD03_sq6_ShortChar01],[UD03_sq6].[Number03] [UD03_sq6_Number03],Number01-Number03 [Calculated_CustomerFinalQty]
from (select [OrderHed1].[Company] [OrderHed1_Company],[OrderHed1].[PONum] [OrderHed1_PONum],[OrderDtl1].[PartNum] [OrderDtl1_PartNum],[OrderDtl1].[OrderNum] [OrderDtl1_OrderNum],[OrderDtl1].[OrderLine] [OrderDtl1_OrderLine],replace([OrderDtl1].[XRevisionNum],'' '','''') [Calculated_OurXRev],[OrderRel1].[OrderRelNum] [OrderRel1_OrderRelNum],[OrderRel1].[OurReqQty] [OrderRel1_OurReqQty],[OrderRel1].[ReqDate] [OrderRel1_ReqDate],[OrderDtl1].[UnitPrice] [OrderDtl1_UnitPrice],[OrderRel1].[SellingReqQty] [OrderRel1_SellingReqQty],[OrderRel1].[OurStockShippedQty] [OrderRel1_OurStockShippedQty],[OrderRel1].[SellingReqQty] - [OrderRel1].[OurStockShippedQty] [Calculated_OurRemainQty]
from ( [Erp].[OrderHed] [OrderHed1] inner join @AvailCompLst [AvailCLst_OrderHed1] on [OrderHed1].[Company] is null  Or [OrderHed1].[Company] = [AvailCLst_OrderHed1].[Company])
inner join [Erp].[OrderDtl] [OrderDtl1] on [OrderHed1].[Company] = [OrderDtl1].[Company] And [OrderHed1].[OrderNum] = [OrderDtl1].[OrderNum]
inner join [Erp].[OrderRel] [OrderRel1] on [OrderDtl1].[Company] = [OrderRel1].[Company] And [OrderDtl1].[OrderNum] = [OrderRel1].[OrderNum] And [OrderDtl1].[OrderLine] = [OrderRel1].[OrderLine]
)  [SubQuery7]
right outer join ( [Ice].[UD03] [UD03_sq6] inner join @AvailCompLst [AvailCLst_UD03_sq6] on [UD03_sq6].[Company] is null  Or [UD03_sq6].[Company] = [AvailCLst_UD03_sq6].[Company]) on [UD03_sq6].[Company] = [SubQuery7].[OrderHed1_Company] And [UD03_sq6].[Key2] = [SubQuery7].[OrderHed1_PONum] And (iif(right([UD03_sq6].[Key3],3)=''---'', left(right([UD03_sq6].[Key3],6),3) + ''-'' + [UD03_sq6].[ShortChar01], left(right([UD03_sq6].[Key3],7),3) + right([UD03_sq6].[Key3],3) + ''-'' + [UD03_sq6].[ShortChar01])) = [SubQuery7].[Calculated_OurXRev] And [UD03_sq6].[Key4] = [SubQuery7].[OrderDtl1_PartNum] And [UD03_sq6].[Number02] = [SubQuery7].[OrderDtl1_UnitPrice] And [UD03_sq6].[Date01] = [SubQuery7].[OrderRel1_ReqDate] And ((Number01-Number03) = [SubQuery7].[Calculated_OurRemainQty] Or [UD03_sq6].[Number01] = [SubQuery7].[OrderRel1_SellingReqQty])
left outer join (select [OrderHed3].[Company] [OrderHed3_Company],[OrderHed3].[PONum] [OrderHed3_PONum],[OrderHed3].[OrderNum] [OrderHed3_OrderNum],[OrderDtl3].[OrderLine] [OrderDtl3_OrderLine],[OrderDtl3].[PartNum] [OrderDtl3_PartNum],[OrderDtl3].[UnitPrice] [OrderDtl3_UnitPrice],replace([OrderDtl3].[XRevisionNum], '' '', '''') [Calculated_OurXRev]
from ( [Erp].[OrderHed] [OrderHed3] inner join @AvailCompLst [AvailCLst_OrderHed3] on [OrderHed3].[Company] is null  Or [OrderHed3].[Company] = [AvailCLst_OrderHed3].[Company])
inner join [Erp].[OrderDtl] [OrderDtl3] on [OrderHed3].[Company] = [OrderDtl3].[Company] And [OrderHed3].[OrderNum] = [OrderDtl3].[OrderNum]
)  [SubQuery9] on [UD03_sq6].[Company] = [SubQuery9].[OrderHed3_Company] And [UD03_sq6].[Key2] = [SubQuery9].[OrderHed3_PONum] And (iif(right([UD03_sq6].[Key3],3)=''---'', left(right([UD03_sq6].[Key3],6),3) + ''-'' + [UD03_sq6].[ShortChar01], left(right([UD03_sq6].[Key3],7),3) + right([UD03_sq6].[Key3],3) + ''-'' + [UD03_sq6].[ShortChar01])) = [SubQuery9].[Calculated_OurXRev] And [UD03_sq6].[Key4] = [SubQuery9].[OrderDtl3_PartNum] And [UD03_sq6].[Number02] = [SubQuery9].[OrderDtl3_UnitPrice]
where [SubQuery7].[OrderHed1_PONum] is null and [SubQuery9].[OrderHed3_PONum] is not null 
group by [UD03_sq6].[Company],iif(right([UD03_sq6].[Key3],3)=''---'', left(right([UD03_sq6].[Key3],6),3) + ''-'' + [UD03_sq6].[ShortChar01], left(right([UD03_sq6].[Key3],7),3) + right([UD03_sq6].[Key3],3) + ''-'' + [UD03_sq6].[ShortChar01]),[UD03_sq6].[Key2],[UD03_sq6].[Key4],[UD03_sq6].[Number01],[UD03_sq6].[Number02],[UD03_sq6].[Key3],[UD03_sq6].[ShortChar01],[UD03_sq6].[Number03],Number01-Number03
)  [SubQuery6] on [SubQuery5].[OrderHed_Company] = [SubQuery6].[UD03_sq6_Company] And [SubQuery5].[OrderHed_PONum] = [SubQuery6].[UD03_sq6_Key2] And [SubQuery5].[Calculated_OurXRev] = [SubQuery6].[Calculated_MyXRev] And [SubQuery5].[OrderDtl_PartNum] = [SubQuery6].[UD03_sq6_Key4] And [SubQuery5].[OrderDtl_UnitPrice] = [SubQuery6].[UD03_sq6_Number02] And ([SubQuery5].[Calculated_OurRemainQty] = [SubQuery6].[Calculated_CustomerFinalQty] Or [SubQuery5].[OrderRel_SellingReqQty] = [SubQuery6].[UD03_sq6_Number01])
left outer join (select [JobHead].[Company] [JobHead_Company],[JobHead].[PartNum] [JobHead_PartNum],[JobHead].[RevisionNum] [JobHead_RevisionNum],min([JobHead].[JobNum]) [Calculated_FirstJob]
from ( [Erp].[JobHead] inner join @AvailCompLst [AvailCLst_JobHead] on [Erp].[JobHead].[Company] is null  Or [Erp].[JobHead].[Company] = [AvailCLst_JobHead].[Company])
where [JobHead].[JobClosed] = 0 
group by [JobHead].[Company],[JobHead].[PartNum],[JobHead].[RevisionNum]
)  [SubQuery10] on [SubQuery5].[OrderHed_Company] = [SubQuery10].[JobHead_Company] And [SubQuery5].[OrderDtl_PartNum] = [SubQuery10].[JobHead_PartNum] And [SubQuery5].[OrderDtl_RevisionNum] = [SubQuery10].[JobHead_RevisionNum]

order by [OrderDtl_PartNum] ,[OrderDtl_OrderNum] ,[OrderDtl_OrderLine] ,[Calculated_FirstRel]  ',N'@_compList [Ice].[QueryParamTableType] READONLY,@CurrentUserID nvarchar(max) ,@CurrentCompany nvarchar(max) ',@_compList=@p3,@CurrentUserID=N'jmitchell',@CurrentCompany=N'01'
SET STATISTICS IO OFF

[NateS (Nate Schoonover)]

Wow! This is really cool! I want to be able to do this with my BAQs as well. I am on a dedicated cloud tennancy. Can I still do this kind of analytics on my BAQ? I can’t get my query execution plan. Is SQL Profiler built into Epicor? Is it a free tool?

Regarding the index fields, I thought that it was faster to include all the indexed fields for a table if possible. So I went back through my slower queries and added company to all my joins and actually saw a little speed boost as a result. Are you saying that I should get rid of the company in the join, and set my table criteria (for every table involved) to filter for company first?

Is it slower to join on calculated fields? In some cases I have to do some string formatting to get the values to join correctly. If this is causing some of my lag, I could sort of front-load this string formatting work, and do more macro-based cleanup to the CSV file before I load it into UD03.

Finally, how can I add an index to a non-Key field in my UD03 table? Say I want to index Number01, Number02, Number03, ShortChar01, and ShortChar02. I am already using all of my key fields for things like PO, XRev, and PartNumber. I would love to index additional fields in my table. Since my total rows generally don’t go above 4000, this shouldn’t tax the servers too much. Right?

Thanks a bunch!

[John_Mitchell]

@NateS Do you get DB access with dedicated tenancy? SQL Profiler is built into SSMS but is being phased out for extended events from my understanding.

You can set BAQ Logging in the trace logging as well as SQL Query Detail in the Epicor Admin console. But it didn’t look like either of those actually give you the query that is executed.

I would never say always just because there will always be some crazy scenario where it’s not, but, yes, calculated fields should be slower in joins.

I honestly don’t know how to add an index to a hosted DB. @Mark_Wonsil is the cloud guy and might have an idea.

One issue that you are going to run into is that your performance with a BAQ is always limited. If you really really need performance (you don’t) then you will need to write the query in SQL and create an indexed view or build a stored procedure where you pass the parameters in.

@Dmitry_Kashulin Can we can add additional Query Test Execution Settings to the BAQ’s and get them to display in the Query Execution Messages? Specifically SET STATISTICS IO, TIME, SHOWPLAN_TEXT and PROFILE. This would allow a hosted user to optimize a BAQ in the application.

[NateS (Nate Schoonover)]

@John_Mitchell

Do you get DB access with dedicated tenancy?

I don’t think so. This all sounds great though. I will keep an eye on this thread.

For now I am going back and forth with company in the table criteria and in the join. Sometimes (like if the table is actually a subquery) the company in the criteria slows it way down. Other times it seems to help. Not sure why this is just yet. I will keep experimenting, but so far I have knocked down the BAQ to under a second!
Thanks!
Nate

[Mark_Wonsil]

@NateS

but so far I have knocked down the BAQ to under a second!

No, you do not have SQL access with any cloud product, including DT. It would be nice (for both cloud and on-prem) to have a model-friendly way to add indexes to the database within the interface.

Mr. Cloud

[Edge (Stephen Edginton)]

is also an option you can review. Custom Indexes for Cloud customers is something that has to be submitted via a support ticket. There is no way yet to define custom indexes in a self service way.

[Mark_Wonsil]

Just an FYI @Edge, when SaaS users perform this command, we get:

I haven’t bothered the Cloud Team to get the error but do you think this should work? If so, I can submit a case.

Thanks!

Mark W.

[Edge (Stephen Edginton)]

Please log a ticket for this - it is probably due to locked down permissions but lets handle the a little better

[Olga (Olga Klimova)]

This action DMV requires special right that is not switched on for regularly DB access.

[Mark_Wonsil]

Ah, do you have to give rights to the whole SQL instance and not individual databases within?

[Olga (Olga Klimova)]

No, the right (I don;t remember the name, but it is in doc), is on by default only for SQL sysadmin and is not set for db users in saas.
This functionality was added to BAQ Designer when no Saas existed yet