Tracking Down a Speed Issue

[NateS (Nate Schoonover)]

Good afternoon,
We have a custom dashboard based on two UBAQs that we use to parse customer orders. In the past this process has worked alright. Recently it has become slower and slower to the point of being almost unusable.

The basic process is to use UD02 to load up a list of orders the customer wants. This list is compared to our sales orders in multiple ways. For example, the customer might have changed the quantity but not the date, or the date but not the quantity. The BAQs in the dashboard show this comparison to the user an allows them to choose how they want to update our sales orders.

Part of this process is an ugly BAQ that joins UD02 to SalesOrder releases on various criteria. It tries to link on part number, quantity, ship to code and date. Depending on what the user decides, the sales order release might be updated with a new date, or quantity, or the release could be closed because there is no longer any demand, or the release might be new and not represented by any open sales order.

Loading the BAQ is taking a long time. In the dashboard it appears to not load at all. I set the timeout to 200, and this at least allowed it to return something in the dashboard, but it takes a long time. I am wondering where I could look to find out what is causing this slow down.

I don’t have any data or method directives touching these tables. We are cloud DT. Part of the BPM for loading data into UD02 involves copying the previous data that was in the table and plopping it into UD04. So at any point in time UD 04 has last week’s data, and UD02 has this week’s data. I have verified that UD04 is not blowing up with too many records. Both UD02 and 04 have procedures in place to delete the old data so that the records don’t add up. Both tables have less than 1000 records each.

1. Are there any temp tables or anything similar that is populated when users load data to a UD table?
2. Is there a way to trace what is going on at the server side to see what is causing the delay? Perhaps it is just one part of my BAQ that is blowing things up?
3. Where else would you look to find issues with this dashboard?

I worry if I post up the BAQ in question it will open a whole can of worms I am not hungry for. However, if you need to see the BAQ to provide advice, I can try to post it.

[gpayne (Greg Payne)]

@NateS Before you have to open the can of worms check that every join and criteria uses company and as many fields of the primary key as possible to help sql pick the right indexes.

I think setting option recompile can also help sometimes in these situations.

If you can uses SSMS, in the baq designer there is actions > Get execution plan that will create a file you can analyze for adding indexes that might help speed up the query.

[NateS (Nate Schoonover)]

I do use company in every join. Since I am using UD02, a lot of my joins hit back to that table. In this case, I don’t have data for some of the key fields. The only thing that matches the key fields in UD02 is the part number in Key4. So many of the joins are just Company and PartNum.
Is it faster to run all the keys with some kind of wide-open criteria? (Key1 LIKE ‘%’)
We are also Cloud DT, so we can’t see the execution plan
What is setting option recompile?
Thank you!

[klincecum (Kevin Lincecum)]

@NateS

What is setting option recompile?

Here is a link to a post with a screenshot, and also a relevant discussion thread.

Some of our Epicor users experience extreme slow downs on loading a BAQ through a customization but when using a different user ID on the same computer, its fast!

I thought BAQ sql queries were recompiled each time? If not, should he try the Option Recompile trick? Edit: In case @josecgomez doesn’t get back to us, here is a relevant thread where he goes into it: [optionrecompile]

[NateS (Nate Schoonover)]

Is this the same as updating statistics for tables? I see Jose has a few lines in SQL for that. Where would I input SQL lines like that?

UPDATE STATISTICS Erp.PartRev WITH FULLSCAN
UPDATE STATISTICS Erp.<YourOtherTables> WITH FULLSCAN

[NateS (Nate Schoonover)]

@NateS

We are also Cloud DT, so we can’t see the execution plan

It looks like I can download the SQL Execution plan now. I have it open in Visual Studio. It looks like an XML file. What am I looking at or looking for in the SQL plan? I see these warnings at the top:

            <Warnings>
              <PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT(varchar(30),[SaaSPilot_].[Erp].[OrderHed].[OrderNum],0)"></PlanAffectingConvert>
              <PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT(varchar(30),[SaaSPilot_].[Erp].[OrderDtl].[OrderLine],0)"></PlanAffectingConvert>
              <PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT(varchar(30),[SaaSPilot_].[Erp].[OrderRel].[OrderRelNum],0)"></PlanAffectingConvert>
              <PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT(int,[SaaSPilot_].[Ice].[UD02].[Key1],0)"></PlanAffectingConvert>
            </Warnings>

[NateS (Nate Schoonover)]

I turned on ShowStatistics, and got this helpful chunk back after running the BAQ:

SQL Server statistics:

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 11752, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'OrderRel'. Scan count 339190, logical reads 5849069, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'OrderHed'. Scan count 1, logical reads 976666, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'OrderDtl'. Scan count 314976, logical reads 978020, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'UD02'. Scan count 7023, logical reads 15475, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'ShipDtl'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 40328 ms,  elapsed time = 42443 ms.

I think this is pointing at OrderRel, OrderDtl, and OrderHed as having the most impact. I need to pull in references to those tables multiple times. In one subquery I return the matches from part number only, in another subquery I return results based on part number and req date, and in another subquery, I return results on part, and dock code (ship to).

[NateS (Nate Schoonover)]

This is the SQL for the BAQ in question. I recently added all those “KeyX LIKE ‘%’” fields to help fill in all the keys. It didn’t help. Nor did setting option recompile. I tried both ways with the OrderBy and QueryOption. It still takes a good 40 seconds to return results.

 
select distinct
	(cast( CustOpenOrders.UD02_Key1 as int)) as [Calculated_SortKey],
	[ CustOpenOrders].[UD02_Key4] as [UD02_Key4],
	[ CustOpenOrders].[UD02_Number01] as [UD02_Number01],
	[ CustOpenOrders].[UD02_Date01] as [UD02_Date01],
	[ CustOpenOrders].[UD02_ShortChar01] as [UD02_ShortChar01],
	[MatchedDates].[OrderDtl_OrderNum] as [OrderDtl_OrderNum],
	[MatchedDates].[OrderRel_OrderLine] as [OrderRel_OrderLine],
	[MatchedDates].[OrderRel_OrderRelNum] as [OrderRel_OrderRelNum],
	[MatchedDates].[OrderRel_OurReqQty] as [OrderRel_OurReqQty],
	(0) as [Calculated_NewRelease],
	(0) as [Calculated_UpdateRelease],
	[MatchedDates].[Calculated_OurShipped] as [Calculated_OurShipped],
	[MatchedDates].[Calculated_SuggOrLnRl] as [Calculated_SuggOrLnRl],
	[MatchedDates].[OrderDtl_LineDesc] as [OrderDtl_LineDesc],
	[MatchedDates].[OrderDtl_RevisionNum] as [OrderDtl_RevisionNum],
	[MatchedDates].[OrderHed_OpenOrder] as [OrderHed_OpenOrder],
	[MatchedDates].[OrderDtl_OpenLine] as [OrderDtl_OpenLine],
	(MatchedDates.OrderRel_OurReqQty- MatchedDates.Calculated_OurShipped) as [Calculated_OurRemain],
	[OurShipped-3].[Calculated_Shipped] as [Calculated_Shipped],
	[OurShipped-3].[ShipHead_ShipDate] as [ShipHead_ShipDate],
	[Our CustOrders2].[OrderRel_OrderLine] as [OrderRel_OrderLine01],
	[Our CustOrders3].[OrderHed_PONum] as [OrderHed_PONum],
	[Our CustOrders2].[OrderDtl_OrderNum] as [OrderDtl_OrderNum01],
	[Our CustOrders3].[OrderDtl_OrderNum] as [OrderDtl_OrderNum02]
from  (select 
	[UD02].[Key1] as [UD02_Key1],
	[UD02].[Key4] as [UD02_Key4],
	[UD02].[Number01] as [UD02_Number01],
	[UD02].[Date01] as [UD02_Date01],
	[UD02].[ShortChar01] as [UD02_ShortChar01],
	[ CustNewOrders].[OrderDtl1_PartNum] as [OrderDtl1_PartNum],
	[ CustNewOrders].[UD02a_Key4] as [UD02a_Key4],
	[UD02].[Company] as [UD02_Company],
	[UD02].[Key2] as [UD02_Key2],
	[UD02].[Key3] as [UD02_Key3],
	[UD02].[Key5] as [UD02_Key5]
from Ice.UD02 as UD02
left outer join  (select 
	[UD02a].[Company] as [UD02a_Company],
	[UD02a].[Key4] as [UD02a_Key4],
	[OrderDtl1].[PartNum] as [OrderDtl1_PartNum],
	[UD02a].[Key1] as [UD02a_Key1],
	[UD02a].[Key2] as [UD02a_Key2],
	[UD02a].[Key3] as [UD02a_Key3],
	[UD02a].[Key5] as [UD02a_Key5]
from Ice.UD02 as UD02a
left outer join Erp.OrderDtl as OrderDtl1 on 
	UD02a.Company = OrderDtl1.Company
	and UD02a.Key4 = OrderDtl1.PartNum
	and UD02a.ShortChar01 = right(XRevisionNum,2)
	and UD02a.Key1 LIKE '%'
	and UD02a.Key2 LIKE '%'
	and UD02a.Key3 LIKE '%'
	and UD02a.Key5 LIKE '%'
	and ( OrderDtl1.Company = @CurrentCompany  and OrderDtl1.OpenLine = true  )

inner join Erp.OrderHed as OrderHed1 on 
	OrderHed1.Company = OrderDtl1.Company
	and OrderHed1.OrderNum = OrderDtl1.OrderNum
	and ( OrderHed1.Company = @CurrentCompany  and OrderHed1.CustNum = 9999  )

where (UD02a.Company = @CurrentCompany))  as  CustNewOrders on 
	UD02.Company =  CustNewOrders.UD02a_Company
	and UD02.Key4 =  CustNewOrders.UD02a_Key4
	and UD02.Key1 LIKE '%'
	and UD02.Key2 LIKE '%'
	and UD02.Key3 LIKE '%'
	and UD02.Key5 LIKE '%'
where (UD02.Company = @CurrentCompany))  as  CustOpenOrders
left outer join  (select 
	[OrderDtl].[OrderNum] as [OrderDtl_OrderNum],
	[OrderRel].[OrderLine] as [OrderRel_OrderLine],
	[OrderRel].[OrderRelNum] as [OrderRel_OrderRelNum],
	[OrderDtl].[LineDesc] as [OrderDtl_LineDesc],
	[OrderDtl].[PartNum] as [OrderDtl_PartNum],
	[OrderRel].[ReqDate] as [OrderRel_ReqDate],
	[OrderRel].[OurReqQty] as [OrderRel_OurReqQty],
	(OrderRel.SellingReqQty - OrderRel.OurStockShippedQty - OrderRel.OurJobShippedQty) as [Calculated_OurRemain],
	(OrderRel.OurJobShippedQty + OrderRel.OurStockShippedQty) as [Calculated_OurShipped],
	(convert(varchar,OrderHed.OrderNum) + '-' + convert(varchar,OrderDtl.OrderLine) + '-' + convert(varchar,OrderRel.OrderRelNum)) as [Calculated_SuggOrLnRl],
	[OrderDtl].[RevisionNum] as [OrderDtl_RevisionNum],
	[OrderHed].[OpenOrder] as [OrderHed_OpenOrder],
	[OrderDtl].[OpenLine] as [OrderDtl_OpenLine],
	[OrderHed].[Company] as [OrderHed_Company],
	[OrderRel].[ShipToNum] as [OrderRel_ShipToNum],
	(iif(right(OrderHed.PONum,7)='-CLOSED',1,0)) as [Calculated_IsClosed],
	[OrderHed].[PONum] as [OrderHed_PONum]
from Erp.OrderHed as OrderHed
inner join Erp.OrderDtl as OrderDtl on 
	OrderHed.Company = OrderDtl.Company
	and OrderHed.OrderNum = OrderDtl.OrderNum
	and ( OrderDtl.Company = @CurrentCompany  and OrderDtl.OpenLine = true  )

inner join Erp.OrderRel as OrderRel on 
	OrderDtl.Company = OrderRel.Company
	and OrderDtl.OrderNum = OrderRel.OrderNum
	and OrderDtl.OrderLine = OrderRel.OrderLine
	and ( OrderRel.Company = @CurrentCompany  and OrderRel.OpenRelease = true  )

where (OrderHed.Company = @CurrentCompany  and OrderHed.CustNum = 9999))  as Our CustOrders on 
	 CustOpenOrders.UD02_Company = Our CustOrders.OrderHed_Company
	and  CustOpenOrders.UD02_Key4 = Our CustOrders.OrderDtl_PartNum
	and  CustOpenOrders.UD02_Date01 = Our CustOrders.OrderRel_ReqDate
	and  CustOpenOrders.UD02_ShortChar01 = Our CustOrders.OrderRel_ShipToNum
	and ( CustOpenOrders.UD02_Number01 = Our CustOrders.OrderRel_OurReqQty
	or  CustOpenOrders.UD02_Number01 = Our CustOrders.Calculated_OurRemain)
	and  CustOpenOrders.UD02_Key2 LIKE '%'
	and  CustOpenOrders.UD02_Key3 LIKE '%'
	and  CustOpenOrders.UD02_Key5 LIKE '%'
	and  CustOpenOrders.UD02_Key1 LIKE '%'
left outer join  (select 
	[ShipDtl].[Company] as [ShipDtl_Company],
	[ShipDtl].[OrderNum] as [ShipDtl_OrderNum],
	[ShipDtl].[OrderLine] as [ShipDtl_OrderLine],
	[ShipDtl].[OrderRelNum] as [ShipDtl_OrderRelNum],
	[ShipHead].[ShipDate] as [ShipHead_ShipDate],
	(ShipDtl.OurInventoryShipQty + ShipDtl.OurJobShipQty) as [Calculated_Shipped]
from Erp.ShipDtl as ShipDtl
inner join Erp.ShipHead as ShipHead on 
	ShipDtl.Company = ShipHead.Company
	and ShipDtl.PackNum = ShipHead.PackNum
	and ( ShipHead.Company = @CurrentCompany  and ShipHead.ShipDate >= dateadd (day, -3, Constants.Today)  )

where (ShipDtl.Company = @CurrentCompany  and ShipDtl.CustNum = 9999))  as OurShipped-3 on 
	Our CustOrders.OrderHed_Company = OurShipped-3.ShipDtl_Company
	and Our CustOrders.OrderDtl_OrderNum = OurShipped-3.ShipDtl_OrderNum
	and Our CustOrders.OrderRel_OrderLine = OurShipped-3.ShipDtl_OrderLine
	and Our CustOrders.OrderRel_OrderRelNum = OurShipped-3.ShipDtl_OrderRelNum
left outer join  (select 
	[ CustOpenOrders1].[UD02_Key1] as [UD02_Key1],
	[Our CustOrders1].[OrderDtl_OrderNum] as [OrderDtl_OrderNum],
	[Our CustOrders1].[OrderRel_OrderLine] as [OrderRel_OrderLine],
	[Our CustOrders1].[OrderRel_OrderRelNum] as [OrderRel_OrderRelNum],
	[ CustOpenOrders1].[UD02_Key4] as [UD02_Key4],
	[Our CustOrders1].[OrderRel_OurReqQty] as [OrderRel_OurReqQty],
	[ CustOpenOrders1].[UD02_Number01] as [UD02_Number01],
	[Our CustOrders1].[Calculated_OurShipped] as [Calculated_OurShipped],
	[Our CustOrders1].[Calculated_SuggOrLnRl] as [Calculated_SuggOrLnRl],
	[Our CustOrders1].[OrderDtl_LineDesc] as [OrderDtl_LineDesc],
	[Our CustOrders1].[OrderDtl_RevisionNum] as [OrderDtl_RevisionNum],
	[Our CustOrders1].[OrderHed_OpenOrder] as [OrderHed_OpenOrder],
	[Our CustOrders1].[OrderDtl_OpenLine] as [OrderDtl_OpenLine],
	[ CustOpenOrders1].[UD02_Company] as [UD02_Company],
	[ CustOpenOrders1].[UD02_Key2] as [UD02_Key2],
	[ CustOpenOrders1].[UD02_Key3] as [UD02_Key3],
	[ CustOpenOrders1].[UD02_Key5] as [UD02_Key5]
from  (select 
	[UD02].[Key1] as [UD02_Key1],
	[UD02].[Key4] as [UD02_Key4],
	[UD02].[Number01] as [UD02_Number01],
	[UD02].[Date01] as [UD02_Date01],
	[UD02].[ShortChar01] as [UD02_ShortChar01],
	[ CustNewOrders].[OrderDtl1_PartNum] as [OrderDtl1_PartNum],
	[ CustNewOrders].[UD02a_Key4] as [UD02a_Key4],
	[UD02].[Company] as [UD02_Company],
	[UD02].[Key2] as [UD02_Key2],
	[UD02].[Key3] as [UD02_Key3],
	[UD02].[Key5] as [UD02_Key5]
from Ice.UD02 as UD02
left outer join  (select 
	[UD02a].[Company] as [UD02a_Company],
	[UD02a].[Key4] as [UD02a_Key4],
	[OrderDtl1].[PartNum] as [OrderDtl1_PartNum],
	[UD02a].[Key1] as [UD02a_Key1],
	[UD02a].[Key2] as [UD02a_Key2],
	[UD02a].[Key3] as [UD02a_Key3],
	[UD02a].[Key5] as [UD02a_Key5]
from Ice.UD02 as UD02a
left outer join Erp.OrderDtl as OrderDtl1 on 
	UD02a.Company = OrderDtl1.Company
	and UD02a.Key4 = OrderDtl1.PartNum
	and UD02a.ShortChar01 = right(XRevisionNum,2)
	and UD02a.Key1 LIKE '%'
	and UD02a.Key2 LIKE '%'
	and UD02a.Key3 LIKE '%'
	and UD02a.Key5 LIKE '%'
	and ( OrderDtl1.Company = @CurrentCompany  and OrderDtl1.OpenLine = true  )

inner join Erp.OrderHed as OrderHed1 on 
	OrderHed1.Company = OrderDtl1.Company
	and OrderHed1.OrderNum = OrderDtl1.OrderNum
	and ( OrderHed1.Company = @CurrentCompany  and OrderHed1.CustNum = 9999  )

where (UD02a.Company = @CurrentCompany))  as  CustNewOrders on 
	UD02.Company =  CustNewOrders.UD02a_Company
	and UD02.Key4 =  CustNewOrders.UD02a_Key4
	and UD02.Key1 LIKE '%'
	and UD02.Key2 LIKE '%'
	and UD02.Key3 LIKE '%'
	and UD02.Key5 LIKE '%'
where (UD02.Company = @CurrentCompany))  as  CustOpenOrders1
inner join  (select 
	[OrderDtl].[OrderNum] as [OrderDtl_OrderNum],
	[OrderRel].[OrderLine] as [OrderRel_OrderLine],
	[OrderRel].[OrderRelNum] as [OrderRel_OrderRelNum],
	[OrderDtl].[LineDesc] as [OrderDtl_LineDesc],
	[OrderDtl].[PartNum] as [OrderDtl_PartNum],
	[OrderRel].[ReqDate] as [OrderRel_ReqDate],
	[OrderRel].[OurReqQty] as [OrderRel_OurReqQty],
	(OrderRel.SellingReqQty - OrderRel.OurStockShippedQty - OrderRel.OurJobShippedQty) as [Calculated_OurRemain],
	(OrderRel.OurJobShippedQty + OrderRel.OurStockShippedQty) as [Calculated_OurShipped],
	(convert(varchar,OrderHed.OrderNum) + '-' + convert(varchar,OrderDtl.OrderLine) + '-' + convert(varchar,OrderRel.OrderRelNum)) as [Calculated_SuggOrLnRl],
	[OrderDtl].[RevisionNum] as [OrderDtl_RevisionNum],
	[OrderHed].[OpenOrder] as [OrderHed_OpenOrder],
	[OrderDtl].[OpenLine] as [OrderDtl_OpenLine],
	[OrderHed].[Company] as [OrderHed_Company],
	[OrderRel].[ShipToNum] as [OrderRel_ShipToNum],
	(iif(right(OrderHed.PONum,7)='-CLOSED',1,0)) as [Calculated_IsClosed],
	[OrderHed].[PONum] as [OrderHed_PONum]
from Erp.OrderHed as OrderHed
inner join Erp.OrderDtl as OrderDtl on 
	OrderHed.Company = OrderDtl.Company
	and OrderHed.OrderNum = OrderDtl.OrderNum
	and ( OrderDtl.Company = @CurrentCompany  and OrderDtl.OpenLine = true  )

inner join Erp.OrderRel as OrderRel on 
	OrderDtl.Company = OrderRel.Company
	and OrderDtl.OrderNum = OrderRel.OrderNum
	and OrderDtl.OrderLine = OrderRel.OrderLine
	and ( OrderRel.Company = @CurrentCompany  and OrderRel.OpenRelease = true  )

where (OrderHed.Company = @CurrentCompany  and OrderHed.CustNum = 9999))  as Our CustOrders1 on 
	 CustOpenOrders1.UD02_Company = Our CustOrders1.OrderHed_Company
	and  CustOpenOrders1.UD02_Key4 = Our CustOrders1.OrderDtl_PartNum
	and  CustOpenOrders1.UD02_Date01 = Our CustOrders1.OrderRel_ReqDate
	and  not  CustOpenOrders1.UD02_Number01 = Our CustOrders1.OrderRel_OurReqQty)  as MatchedDates on 
	 CustOpenOrders.UD02_Company = MatchedDates.UD02_Company
	and  CustOpenOrders.UD02_Key1 = MatchedDates.UD02_Key1
	and  CustOpenOrders.UD02_Key4 LIKE '%'
	and  CustOpenOrders.UD02_Key2 LIKE '%'
	and  CustOpenOrders.UD02_Key3 LIKE '%'
	and  CustOpenOrders.UD02_Key5 LIKE '%'
left outer join  (select 
	[OrderDtl].[OrderNum] as [OrderDtl_OrderNum],
	[OrderRel].[OrderLine] as [OrderRel_OrderLine],
	[OrderRel].[OrderRelNum] as [OrderRel_OrderRelNum],
	[OrderDtl].[LineDesc] as [OrderDtl_LineDesc],
	[OrderDtl].[PartNum] as [OrderDtl_PartNum],
	[OrderRel].[ReqDate] as [OrderRel_ReqDate],
	[OrderRel].[OurReqQty] as [OrderRel_OurReqQty],
	(OrderRel.SellingReqQty - OrderRel.OurStockShippedQty - OrderRel.OurJobShippedQty) as [Calculated_OurRemain],
	(OrderRel.OurJobShippedQty + OrderRel.OurStockShippedQty) as [Calculated_OurShipped],
	(convert(varchar,OrderHed.OrderNum) + '-' + convert(varchar,OrderDtl.OrderLine) + '-' + convert(varchar,OrderRel.OrderRelNum)) as [Calculated_SuggOrLnRl],
	[OrderDtl].[RevisionNum] as [OrderDtl_RevisionNum],
	[OrderHed].[OpenOrder] as [OrderHed_OpenOrder],
	[OrderDtl].[OpenLine] as [OrderDtl_OpenLine],
	[OrderHed].[Company] as [OrderHed_Company],
	[OrderRel].[ShipToNum] as [OrderRel_ShipToNum],
	(iif(right(OrderHed.PONum,7)='-CLOSED',1,0)) as [Calculated_IsClosed],
	[OrderHed].[PONum] as [OrderHed_PONum]
from Erp.OrderHed as OrderHed
inner join Erp.OrderDtl as OrderDtl on 
	OrderHed.Company = OrderDtl.Company
	and OrderHed.OrderNum = OrderDtl.OrderNum
	and ( OrderDtl.Company = @CurrentCompany  and OrderDtl.OpenLine = true  )

inner join Erp.OrderRel as OrderRel on 
	OrderDtl.Company = OrderRel.Company
	and OrderDtl.OrderNum = OrderRel.OrderNum
	and OrderDtl.OrderLine = OrderRel.OrderLine
	and ( OrderRel.Company = @CurrentCompany  and OrderRel.OpenRelease = true  )

where (OrderHed.Company = @CurrentCompany  and OrderHed.CustNum = 9999))  as Our CustOrders2 on 
	 CustOpenOrders.UD02_Company = Our CustOrders2.OrderHed_Company
	and  CustOpenOrders.UD02_Key4 = Our CustOrders2.OrderDtl_PartNum
	and  CustOpenOrders.UD02_ShortChar01 = Our CustOrders2.OrderRel_ShipToNum
	and  CustOpenOrders.UD02_Key2 LIKE '%'
	and  CustOpenOrders.UD02_Key3 LIKE '%'
	and  CustOpenOrders.UD02_Key4 LIKE '%'
	and  CustOpenOrders.UD02_Key5 LIKE '%'
	and ( Our CustOrders2.Calculated_IsClosed = false  )

left outer join  (select 
	[OrderDtl].[OrderNum] as [OrderDtl_OrderNum],
	[OrderRel].[OrderLine] as [OrderRel_OrderLine],
	[OrderRel].[OrderRelNum] as [OrderRel_OrderRelNum],
	[OrderDtl].[LineDesc] as [OrderDtl_LineDesc],
	[OrderDtl].[PartNum] as [OrderDtl_PartNum],
	[OrderRel].[ReqDate] as [OrderRel_ReqDate],
	[OrderRel].[OurReqQty] as [OrderRel_OurReqQty],
	(OrderRel.SellingReqQty - OrderRel.OurStockShippedQty - OrderRel.OurJobShippedQty) as [Calculated_OurRemain],
	(OrderRel.OurJobShippedQty + OrderRel.OurStockShippedQty) as [Calculated_OurShipped],
	(convert(varchar,OrderHed.OrderNum) + '-' + convert(varchar,OrderDtl.OrderLine) + '-' + convert(varchar,OrderRel.OrderRelNum)) as [Calculated_SuggOrLnRl],
	[OrderDtl].[RevisionNum] as [OrderDtl_RevisionNum],
	[OrderHed].[OpenOrder] as [OrderHed_OpenOrder],
	[OrderDtl].[OpenLine] as [OrderDtl_OpenLine],
	[OrderHed].[Company] as [OrderHed_Company],
	[OrderRel].[ShipToNum] as [OrderRel_ShipToNum],
	(iif(right(OrderHed.PONum,7)='-CLOSED',1,0)) as [Calculated_IsClosed],
	[OrderHed].[PONum] as [OrderHed_PONum]
from Erp.OrderHed as OrderHed
inner join Erp.OrderDtl as OrderDtl on 
	OrderHed.Company = OrderDtl.Company
	and OrderHed.OrderNum = OrderDtl.OrderNum
	and ( OrderDtl.Company = @CurrentCompany  and OrderDtl.OpenLine = true  )

inner join Erp.OrderRel as OrderRel on 
	OrderDtl.Company = OrderRel.Company
	and OrderDtl.OrderNum = OrderRel.OrderNum
	and OrderDtl.OrderLine = OrderRel.OrderLine
	and ( OrderRel.Company = @CurrentCompany  and OrderRel.OpenRelease = true  )

where (OrderHed.Company = @CurrentCompany  and OrderHed.CustNum = 9999))  as Our CustOrders3 on 
	 CustOpenOrders.UD02_Company = Our CustOrders3.OrderHed_Company
	and  CustOpenOrders.UD02_Key4 = Our CustOrders3.OrderDtl_PartNum
	and  CustOpenOrders.UD02_Key1 LIKE '%'
	and  CustOpenOrders.UD02_Key2 LIKE '%'
	and  CustOpenOrders.UD02_Key3 LIKE '%'
	and  CustOpenOrders.UD02_Key5 LIKE '%'
	and ( Our CustOrders3.Calculated_IsClosed = false  )

where (Our CustOrders.OrderDtl_PartNum is null and  CustOpenOrders.OrderDtl1_PartNum is not null)
order by SortKey

[gpayne (Greg Payne)]

@NateS

UPDATE STATISTICS Erp.PartRev WITH FULLSCAN
UPDATE STATISTICS Erp.<YourOtherTables> WITH FULLSCAN

These would be part of a maintenance plan done on the server, so hopefully they are being done already.

[klincecum (Kevin Lincecum)]

@NateS

I worry if I post up the BAQ in question it will open a whole can of worms I am not hungry for.

@NateS

This is the SQL for the BAQ in question.

I know you’re not hungry for it and just want an answer for your technical questions, but with a query that complex, I wouldn’t put off those questions too long.

With all the whizzes around here, there might be a better approach to accomplish your goals.

[kananga (John K)]

@gpayne

These would be part of a maintenance plan done on the server, so hopefully they are being done already.

(laughs in DBA)

Nothing specific to Epicor, just experience.

[NateS (Nate Schoonover)]

@gpayne

These would be part of a maintenance plan done on the server, so hopefully they are being done already.

How would I know if this was already happening? If it is not happening, can I set it up, or does Epicor Support do that?

[gpayne (Greg Payne)]

Support would, but I would ask if it is happening. Also I would ask them to look at the execution plan to see if there are any additional indexes that would help.

Where did you set ShowStatistics?

In your stats above you have almost 6 million reads on the release table. How much data is this dragging thru. Also what is the table order? That can have an impact.

I also noticed you have distinct in the query that can cause performance issues. Were you getting duplicate rows without it? If so it would be better to work the joins to eliminate them.

[NateS (Nate Schoonover)]

@gpayne

Where did you set ShowStatistics?

I set this in Execution Settings.

The table order is a mess. But I am only using UD02, OrderHed/Dtl/Rel, and ShipHed/Dtl.

I was getting duplicates without using Distinct. I will see if I can eliminate that.

Thank you for your time!

[Mark_Wonsil]

So, there are a LOT of LIKE ‘%’ operators in there. You know what fast queries don’t like? LIKE operators.

What I like to do is to start with the most restrictive query first. That way, SQL doesn’t have to match as many records. This becomes a subquery and then I add the next lowest count table, etc. I sense this is all on one subquery. Breaking it up will also reveal where the duplicates are coming from or at least will be easier to eliminate them. It will also give SQL some hints on how the query works. Food for thought.

[Banderson (Brandon Anderson)]

The order of the table can make a difference as well. There is a table list in the query designer, and you can change the order of the tables. Changing these can help SQL find a better plan.

Next thing to try, is that since you are using the same table a bunch, you can make that subquery a CTE because that will essentially load a temporary table in memory to use instead of going back to the actual table every time. So if you can make the first subqeury a CTE that’s parred down as much as you can, then join to that CTE instead of to the actual table.

It may or may not work, but you can try it out.

side note before anyone asks the question, CTE does NOT have to have be only for hierarchical data. It can be, but also has other uses, like the one I just outlined

Here’s the first article that came up when I googled “SQL what’s a CTE query”

What Is a Common Table Expression (CTE) in SQL?

[gpayne (Greg Payne)]

You probably can lose all of the Keyx likes since they made no difference. I would assume the duplicated are coming from ShipDtl. If you do that as a subquery grouped by pack/order/line/release you should get one row. If you are unsure use @Banderson method of adding the sysrowID of all of the tables and see which ones are duplicated.

Are all of the [Our CustOrders] getting a status for each release? Could you do it in one sub and a case statement?

[NateS (Nate Schoonover)]

Thank you all for the great feedback. I had no idea you could also use CTEs that way. I will do some tests today to see if I can get this to speed up. I’ll post back what works and not. I have rebuilt this BAQ many times. Maybe this time it will get better!

[NateS (Nate Schoonover)]

@Banderson

The order of the table can make a difference as well. There is a table list in the query designer, and you can change the order of the tables. Changing these can help SQL find a better plan.

I normally try to change the order from top to bottom. I put Hed first, then Dtl, then Rel.
How can I better decide which tables go first?
How does the table order matter when I am dealing with subqueries?
Should UD tables be before or after Erp tables?
I am working on a copy of the BAQ using CTEs, but I barely understand them. It is slow going. Thank you for your time and expertise!

[Mark_Wonsil]

@NateS

How can I better decide which tables go first?

Put the subquery that returns the fewest records first.