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