So, we are struggling with a BAQ (an ugly one) that does a bunch of calculations and whatnot. If I run the BAQ from within epicor, it takes over 7 minutes to finish (if it finishes). I can grab the query that’s being run from SQL, and wire up the territory stuff and other parameters that’s being used to run it, and it runs in 3 seconds.
We’ve added option (recompile), re-indexed the applicable tables, and recycled the appservers, copied the query, changed inner subs to CTEs, but we still can’t figure out why when running this BAQ in epicor it takes so long, while we can run it in SSMS and it’s only a few seconds.
Grasping at straws here for someone with any ideas.
Here’s the query phrase from the general tab, not that I think it will help.
/*
* 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
[OldTopLevel].[Calculated_Grouping] as [Calculated_Grouping],
[OldTopLevel].[OrderRel_Company] as [OrderRel_Company],
[OldTopLevel].[OrderHed_CustNum] as [OrderHed_CustNum],
[OldTopLevel].[Customer_CustID] as [Customer_CustID],
[OldTopLevel].[Customer_Name] as [Customer_Name],
[OldTopLevel].[OrderHed_PONum] as [OrderHed_PONum],
[OldTopLevel].[OrderRel_OrderNum] as [OrderRel_OrderNum],
[OldTopLevel].[OrderRel_OrderLine] as [OrderRel_OrderLine],
[OldTopLevel].[Calculated_ExistingPackSlips01] as [Calculated_ExistingPackSlips01],
[OldTopLevel].[OrderRel_OrderRelNum] as [OrderRel_OrderRelNum],
[OldTopLevel].[OrderRel_WarehouseCode] as [OrderRel_WarehouseCode],
[OldTopLevel].[Warehse_Top_Description] as [Warehse_Top_Description],
[OldTopLevel].[OrderRel_ShipToNum] as [OrderRel_ShipToNum],
[OldTopLevel].[ShipTo_Name] as [ShipTo_Name],
[OldTopLevel].[OrderRel_ShipViaCode] as [OrderRel_ShipViaCode],
[OldTopLevel].[OrderRel_OurReqQty] as [OrderRel_OurReqQty],
[OldTopLevel].[Calculated_TotalAlreadyShipped] as [Calculated_TotalAlreadyShipped],
[OldTopLevel].[Calculated_MultiWarehouseShipping] as [Calculated_MultiWarehouseShipping],
[OldTopLevel].[Calculated_LeftToShip] as [Calculated_LeftToShip],
[OldTopLevel].[OrderRel_SalesUM] as [OrderRel_SalesUM],
[OldTopLevel].[OrderRel_PartNum] as [OrderRel_PartNum],
[OldTopLevel].[Part_PartNum] as [Part_PartNum],
[OldTopLevel].[Part_PartDescription] as [Part_PartDescription],
[OldTopLevel].[Calculated_TotalQtyOnHand] as [Calculated_TotalQtyOnHand],
[OldTopLevel].[Calculated_AvailableQty] as [Calculated_AvailableQty],
[OldTopLevel].[Calculated_QtyToShip] as [Calculated_QtyToShip],
[OldTopLevel].[Calculated_ErrorsField] as [Calculated_ErrorsField],
[OldTopLevel].[OrderRel_ReqDate] as [OrderRel_ReqDate],
[OldTopLevel].[Calculated_ShipByTo] as [Calculated_ShipByTo],
[OldTopLevel].[OrderRel_NeedByDate] as [OrderRel_NeedByDate],
[OldTopLevel].[Calculated_NeedByTo] as [Calculated_NeedByTo],
[OldTopLevel].[OrderHed_OrderDate] as [OrderHed_OrderDate],
[OldTopLevel].[Calculated_FillPriority] as [Calculated_FillPriority],
[OldTopLevel].[Calculated_AfterProcessInfo] as [Calculated_AfterProcessInfo],
[OldTopLevel].[Calculated_ExistingPackSlips] as [Calculated_ExistingPackSlips],
[OldTopLevel].[Calculated_ReqRunningSum] as [Calculated_ReqRunningSum],
[OldTopLevel].[Calculated_RunningAvailQty] as [Calculated_RunningAvailQty],
(case when
(sum(
case when (OldTopLevel.Calculated_RunningAvailQty - OldTopLevel.Calculated_LeftToShip) < 0 then 1
when OldTopLevel.Part_PartNum is null then 1
else 0
end) over (partition by OldTopLevel.OrderRel_OrderNum)) > 0 then 0
else 1
end) as [Calculated_FullOrderShipping],
[OldTopLevel].[CrossDock_CrossDockedQty] as [CrossDock_CrossDockedQty],
[OldTopLevel].[Calculated_PickingSum] as [Calculated_PickingSum],
[OldTopLevel].[Calculated_PickedSum] as [Calculated_PickedSum],
[OldTopLevel].[Calculated_ReservedSum] as [Calculated_ReservedSum],
[OldTopLevel].[Calculated_AllocatedSum] as [Calculated_AllocatedSum],
[OldTopLevel].[Calculated_Bins] as [Calculated_Bins],
(case when
(sum(
case when (isnull(OldTopLevel.Calculated_AllocatedSum,0) < OldTopLevel.Calculated_LeftToShip) then 1
when OldTopLevel.Part_PartNum is null then 1
else 0
end) over (partition by OldTopLevel.OrderRel_OrderNum)) > 0 then 0
else 1
end) as [Calculated_FullOrderAllocated],
[OldTopLevel].[Calculated_LeadDays] as [Calculated_LeadDays],
(case when FullOrderShipping = 1 then 'True'
else 'False'
end) as [Calculated_FullOrderShippingDropdown],
(case when FullOrderAllocated = 1 then 'True'
else 'False'
end) as [Calculated_FullOrderAllocatedDropDown],
[OldTopLevel].[Warehse_Top_WarehouseCode] as [Warehse_Top_WarehouseCode],
[OldTopLevel].[Calculated_NotThisOrder] as [Calculated_NotThisOrder]
from (select
(Customer.Name + ' ' + ' Order:' + convert(varchar, OrderHed.OrderNum) + ' Line: '+ convert(varchar, OrderRel.OrderLine) +' Release: '+ convert(varchar, OrderRel.OrderRelNum)) as [Calculated_Grouping],
[OrderRel].[Company] as [OrderRel_Company],
[OrderHed].[CustNum] as [OrderHed_CustNum],
[Customer].[CustID] as [Customer_CustID],
[Customer].[Name] as [Customer_Name],
[OrderHed].[PONum] as [OrderHed_PONum],
[OrderRel].[OrderNum] as [OrderRel_OrderNum],
[OrderRel].[OrderLine] as [OrderRel_OrderLine],
[QtyAlreadyShipped].[Calculated_ExistingPackSlips] as [Calculated_ExistingPackSlips01],
[OrderRel].[OrderRelNum] as [OrderRel_OrderRelNum],
[OrderRel].[WarehouseCode] as [OrderRel_WarehouseCode],
[Warehse_Top].[Description] as [Warehse_Top_Description],
[OrderRel].[ShipToNum] as [OrderRel_ShipToNum],
[ShipTo].[Name] as [ShipTo_Name],
[OrderRel].[ShipViaCode] as [OrderRel_ShipViaCode],
[OrderRel].[OurReqQty] as [OrderRel_OurReqQty],
[QtyAlreadyShipped].[Calculated_TotalAlreadyShipped] as [Calculated_TotalAlreadyShipped],
[QtyAlreadyShipped].[Calculated_MultiWarehouseShipping] as [Calculated_MultiWarehouseShipping],
(case when Part.PartNum is null then 0
else
OrderRel.OurReqQty - isnull(QtyAlreadyShipped.Calculated_TotalAlreadyShipped,0)
end) as [Calculated_LeftToShip],
[OrderRel].[SalesUM] as [OrderRel_SalesUM],
[OrderRel].[PartNum] as [OrderRel_PartNum],
[Part].[PartNum] as [Part_PartNum],
[Part].[PartDescription] as [Part_PartDescription],
[PartBinInfo].[Calculated_TotalQtyOnHand] as [Calculated_TotalQtyOnHand],
(isnull(PartBinInfo.Calculated_TotalQtyOnHand,0) - isnull(PartBinInfo.Calculated_TotalShipping,0)- isnull(NotThisOrder,0)) as [Calculated_AvailableQty],
(0) as [Calculated_QtyToShip],
('') as [Calculated_ErrorsField],
[OrderRel].[ReqDate] as [OrderRel_ReqDate],
(OrderRel.ReqDate) as [Calculated_ShipByTo],
[OrderRel].[NeedByDate] as [OrderRel_NeedByDate],
(OrderRel.NeedByDate) as [Calculated_NeedByTo],
[OrderHed].[OrderDate] as [OrderHed_OrderDate],
(row_number() over (partition by OrderRel.PartNum, OrderRel.WarehouseCode order by
isnull(OrderRel.ReqDate, dateadd(year, 1, Constants.Today)) asc,
OrderHed.OrderDate asc, OrderHed.OrderNum asc, OrderRel.OrderLine asc )) as [Calculated_FillPriority],
('') as [Calculated_AfterProcessInfo],
[PartBinInfo].[Calculated_ExistingPackSlips] as [Calculated_ExistingPackSlips],
(sum(LeftToShip) over (partition by OrderRel.PartNum, orderRel.WarehouseCode order by
isnull(OrderRel.ReqDate, dateadd(year, 1, Constants.Today)) asc, OrderHed.OrderDate asc, OrderHed.OrderNum asc, OrderRel.OrderLine asc)) as [Calculated_ReqRunningSum],
(case when AvailableQty - ReqRunningSum + LeftToShip > 0 then AvailableQty - ReqRunningSum + LeftToShip
else 0
end) as [Calculated_RunningAvailQty],
[CrossDock].[CrossDockedQty] as [CrossDock_CrossDockedQty],
[PartAllocInfo].[Calculated_PickingSum] as [Calculated_PickingSum],
[PartAllocInfo].[Calculated_PickedSum] as [Calculated_PickedSum],
[PartAllocInfo].[Calculated_ReservedSum] as [Calculated_ReservedSum],
[PartAllocInfo].[Calculated_AllocatedSum] as [Calculated_AllocatedSum],
[PartAllocInfo].[Calculated_Bins] as [Calculated_Bins],
((DATEDIFF(dd, OrderHed.OrderDate, OrderRel.NeedByDate))
/*
-(DATEDIFF(wk, OrderHed.OrderDate, OrderRel.ReqDate) * 2)
-(CASE WHEN DATENAME(dw, OrderHed.OrderDate) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, OrderRel.ReqDate) = 'Saturday' THEN 1 ELSE 0 END)
*/
--this extra stuff is for removing weekends) as [Calculated_LeadDays],
[Warehse_Top].[Plant] as [Warehse_Top_Plant],
[Warehse_Top].[WarehouseCode] as [Warehse_Top_WarehouseCode],
(((select
(sum(PartAlloc_other.AllocatedQty)) as [Calculated_AllocatedToOther]
from Erp.PartAlloc as PartAlloc_other
where (PartAlloc_other.Company = OrderRel.Company and PartAlloc_other.PartNum = PartBinInfo.PartBin_PartNum and PartAlloc_other.WarehouseCode = PartBinInfo.PartBin_WarehouseCode and (PartAlloc_other.OrderNum <> OrderRel.OrderNum or PartAlloc_other.OrderLine <> OrderRel.OrderLine or PartAlloc_other.OrderRelNum <> OrderRel.OrderRelNum ))))) as [Calculated_NotThisOrder]
from Erp.OrderRel as OrderRel
inner join Erp.OrderHed as OrderHed on
OrderRel.Company = OrderHed.Company
and OrderRel.OrderNum = OrderHed.OrderNum
and ( OrderHed.OpenOrder = 1 )
inner join Erp.Customer as Customer on
OrderHed.Company = Customer.Company
and OrderHed.CustNum = Customer.CustNum
inner join Erp.ShipTo as ShipTo on
OrderRel.Company = ShipTo.Company
and OrderRel.ShipToNum = ShipTo.ShipToNum
inner join Erp.ShipTo as ShipTo
and
Customer.Company = ShipTo.Company
and Customer.CustNum = ShipTo.CustNum
left outer join (select
[ShipDtl_ThisOrder].[Company] as [ShipDtl_ThisOrder_Company],
[ShipDtl_ThisOrder].[OrderNum] as [ShipDtl_ThisOrder_OrderNum],
[ShipDtl_ThisOrder].[OrderLine] as [ShipDtl_ThisOrder_OrderLine],
[ShipDtl_ThisOrder].[OrderRelNum] as [ShipDtl_ThisOrder_OrderRelNum],
(sum(isnull(ShipDtl_ThisOrder.OurInventoryShipQty,0)+isnull(ShipDtl_ThisOrder.OurJobShipQty,0))) as [Calculated_TotalAlreadyShipped],
(string_agg(ShipDtl_ThisOrder.PackNum, ', ')) as [Calculated_ExistingPackSlips],
(case when count (distinct ShipDtl_ThisOrder.WarehouseCode) > 1 then 1
else 0
end) as [Calculated_MultiWarehouseShipping]
from Erp.ShipDtl as ShipDtl_ThisOrder
group by [ShipDtl_ThisOrder].[Company],
[ShipDtl_ThisOrder].[OrderNum],
[ShipDtl_ThisOrder].[OrderLine],
[ShipDtl_ThisOrder].[OrderRelNum]) as QtyAlreadyShipped on
OrderRel.Company = QtyAlreadyShipped.ShipDtl_ThisOrder_Company
and OrderRel.OrderNum = QtyAlreadyShipped.ShipDtl_ThisOrder_OrderNum
and OrderRel.OrderLine = QtyAlreadyShipped.ShipDtl_ThisOrder_OrderLine
and OrderRel.OrderRelNum = QtyAlreadyShipped.ShipDtl_ThisOrder_OrderRelNum
left outer join (select
[PartBin].[Company] as [PartBin_Company],
[PartBin].[PartNum] as [PartBin_PartNum],
[PartBin].[WarehouseCode] as [PartBin_WarehouseCode],
[OtherPackSlips].[Calculated_ExistingPackSlips] as [Calculated_ExistingPackSlips],
[OtherPackSlips].[Calculated_TotalShipping] as [Calculated_TotalShipping],
(sum(PartBin.OnhandQty)) as [Calculated_TotalQtyOnHand]
from Erp.PartBin as PartBin
left outer join (select
[ShipDtl].[Company] as [ShipDtl_Company],
[ShipDtl].[PartNum] as [ShipDtl_PartNum],
[ShipDtl].[WarehouseCode] as [ShipDtl_WarehouseCode],
(sum(ShipDtl.OurInventoryShipQty)) as [Calculated_TotalShipping],
(string_agg(ShipHead.PackNum, ', ')) as [Calculated_ExistingPackSlips]
from Erp.ShipDtl as ShipDtl
inner join Erp.ShipHead as ShipHead on
ShipDtl.Company = ShipHead.Company
and ShipDtl.PackNum = ShipHead.PackNum
and ( ShipHead.ReadyToInvoice = false )
group by [ShipDtl].[Company],
[ShipDtl].[PartNum],
[ShipDtl].[WarehouseCode]) as OtherPackSlips on
PartBin.Company = OtherPackSlips.ShipDtl_Company
and PartBin.PartNum = OtherPackSlips.ShipDtl_PartNum
and PartBin.WarehouseCode = OtherPackSlips.ShipDtl_WarehouseCode
inner join Erp.Warehse as Warehse on
PartBin.Company = Warehse.Company
and PartBin.WarehouseCode = Warehse.WarehouseCode
and ( Warehse.Plant = @CurrentPlant )
where (PartBin.PCID = '')
group by [PartBin].[Company],
[PartBin].[PartNum],
[PartBin].[WarehouseCode],
[OtherPackSlips].[Calculated_ExistingPackSlips],
[OtherPackSlips].[Calculated_TotalShipping]) as PartBinInfo on
OrderRel.Company = PartBinInfo.PartBin_Company
and OrderRel.PartNum = PartBinInfo.PartBin_PartNum
and OrderRel.WarehouseCode = PartBinInfo.PartBin_WarehouseCode
left outer join Erp.Part as Part on
OrderRel.Company = Part.Company
and OrderRel.PartNum = Part.PartNum
inner join Erp.Warehse as Warehse_Top on
OrderRel.Company = Warehse_Top.Company
and (OrderRel.WarehouseCode = Warehse_Top.WarehouseCode
or OrderRel.WarehouseCode = Null)
and ( Warehse_Top.Plant = @CurrentPlant )
left outer join Erp.CrossDock as CrossDock on
OrderRel.Company = CrossDock.Company
and OrderRel.OrderNum = CrossDock.OrderNum
and OrderRel.OrderLine = CrossDock.OrderLine
and OrderRel.OrderRelNum = CrossDock.OrderRelNum
left outer join (select
[PartAlloc].[Company] as [PartAlloc_Company],
[PartAlloc].[PartNum] as [PartAlloc_PartNum],
[PartAlloc].[WarehouseCode] as [PartAlloc_WarehouseCode],
[PartAlloc].[OrderNum] as [PartAlloc_OrderNum],
[PartAlloc].[OrderLine] as [PartAlloc_OrderLine],
[PartAlloc].[OrderRelNum] as [PartAlloc_OrderRelNum],
(sum(PartAlloc.PickingQty)) as [Calculated_PickingSum],
(sum(PartAlloc.PickedQty)) as [Calculated_PickedSum],
(sum(PartAlloc.ReservedQty)) as [Calculated_ReservedSum],
(sum(PartAlloc.AllocatedQty)) as [Calculated_AllocatedSum],
(string_agg (PartAlloc.BinNum,', ')) as [Calculated_Bins]
from Erp.PartAlloc as PartAlloc
group by [PartAlloc].[Company],
[PartAlloc].[PartNum],
[PartAlloc].[WarehouseCode],
[PartAlloc].[OrderNum],
[PartAlloc].[OrderLine],
[PartAlloc].[OrderRelNum]) as PartAllocInfo on
OrderRel.Company = PartAllocInfo.PartAlloc_Company
and OrderRel.OrderNum = PartAllocInfo.PartAlloc_OrderNum
and OrderRel.OrderLine = PartAllocInfo.PartAlloc_OrderLine
and OrderRel.OrderRelNum = PartAllocInfo.PartAlloc_OrderRelNum
where (OrderRel.Plant = @CurrentPlant)) as OldTopLevel
