BAQ Timeout - Why?

[Mike (Mike Watkinson)]

I have a BAQ which has a few layers of sub-queries which are then pulled into a top level query which is based off of the GL period balance table. When I run the top level without bringing in one of the sub-queries, I am at 272ms. When I bring in the sub-query and only display a field that has less going on in the sub-query, lets say company, I am at 282ms. If I then display a field which is a slightly more complex calculated field in the sub-query, the BAQ times out.

If I set that sub-query as the top level to test it, then it runs fine in 377ms.

Any way to fix something like this or any idea why that would happen? Does it need to run the sub-query separately for each record on the top level query or something?

[Craig_Moore]

Can you show the join between the top level and the subquery?
Also, try outer join if you aren’t already.

[Banderson (Brandon Anderson)]

It can depends on your joins. Are you bring in Company from every table? That can make a big difference.

[Randy (Randy Stulce)]

You can adjust the BAQ timeout as well. We have one that’s well designed it’s just needing to pull a bunch of data from the GL so times out until we changed the timeout.

[knash (Ken Nash)]

Posting your query phrase of the BAQ would help us, help you.

[Mike (Mike Watkinson)]

@Banderson I did not bring company in the join on every table. We only have 1 company so I miss it sometimes. I will try to go add it on all the joins and see if it helps.

@Craig_Moore here is the join on the top level and the sub-query. As I said, the join works when I only display the year and month fields.

@knash below is the query phrase.

select 
	[GLPeriodBal].[BalanceAcct] as [GLPeriodBal_BalanceAcct],
	[GLPeriodBal].[FiscalYear] as [GLPeriodBal_FiscalYear],
	[GLPeriodBal].[FiscalPeriod] as [GLPeriodBal_FiscalPeriod],
	[PB_ShipPrevAdvInv].[GLAccount_AccountDesc] as [GLAccount_AccountDesc],
	[PB_ShipPrevAdvInv].[Calculated_ShipCurrentInvPast] as [Calculated_ShipCurrentInvPast]
from Erp.GLPeriodBal as GLPeriodBal
left outer join  (select 
	[ShipInvPrevComplete].[Calculated_Year] as [Calculated_Year],
	[ShipInvPrevComplete].[Calculated_Month] as [Calculated_Month],
	[ShipInvPrevComplete].[GLCntrlAcct_GLAccount] as [GLCntrlAcct_GLAccount],
	[ShipInvPrevComplete].[GLAccount_AccountDesc] as [GLAccount_AccountDesc],
	(Sum(ShipInvPrevComplete.Calculated_ReleasedPrevCompAdvBill)) as [Calculated_ShipCurrentInvPast]
from  (select 
	[InvcHead].[ApplyDate] as [InvcHead_ApplyDate],
	[InvcDtl].[InvoiceNum] as [InvcDtl_InvoiceNum],
	[InvcDtl].[InvoiceLine] as [InvcDtl_InvoiceLine],
	[InvcDtl].[OrderNum] as [InvcDtl_OrderNum],
	[InvcDtl].[OrderLine] as [InvcDtl_OrderLine],
	[InvcDtl].[OrderRelNum] as [InvcDtl_OrderRelNum],
	(Max(InvcDtl.ExtPrice)) as [Calculated_ExtPrice],
	(Max(InvcDtl.DocAdvanceBillCredit)) as [Calculated_AdvBillCredit],
	(sum(case
 when DefRevAdvBillLine.Calculated_TranDateEOM <= EOMONTH(InvcHead.ApplyDate) then DefRevAdvBillLine.Calculated_AdjCompleteOrderTotal
 else 0
 end)) as [Calculated_PrevCompleteAdvBill],
	(case
 when AdvBillCredit = 0 then 0
 when PrevCompleteAdvBill = 0 then 0
 when PrevCompleteAdvBill > AdvBillCredit then AdvBillCredit
 else PrevCompleteAdvBill
 end) as [Calculated_ReleasedPrevCompAdvBill],
	[OrderGLAccnt].[GLCntrlAcct_GLAccount] as [GLCntrlAcct_GLAccount],
	[OrderGLAccnt].[GLAccount_AccountDesc] as [GLAccount_AccountDesc],
	(datepart(year,Max(InvcHead.ApplyDate))) as [Calculated_Year],
	(datepart(month,Max(InvcHead.ApplyDate))) as [Calculated_Month]
from Erp.InvcDtl as InvcDtl
inner join Erp.InvcHead as InvcHead on 
	InvcDtl.Company = InvcHead.Company
	and InvcDtl.InvoiceNum = InvcHead.InvoiceNum
	and ( InvcHead.InvoiceType <> 'ADV'  )

left outer join  (select 
	[MiddleDetail].[InvcDtl1_OrderNum] as [InvcDtl1_OrderNum],
	[MiddleDetail].[InvcDtl1_OrderLine] as [InvcDtl1_OrderLine],
	[MiddleDetail].[InvcDtl1_OrderRelNum] as [InvcDtl1_OrderRelNum],
	[MiddleDetail].[Calculated_TranDateEOM] as [Calculated_TranDateEOM],
	[MiddleDetail].[Calculated_RowNum] as [Calculated_RowNum],
	[MiddleDetail].[Calculated_OrderLineRel] as [Calculated_OrderLineRel],
	[MiddleDetail].[Calculated_RunningTotal] as [Calculated_RunningTotal],
	[MiddleDetail].[OrderDtl_DocUnitPrice] as [OrderDtl_DocUnitPrice],
	[MiddleDetail].[Calculated_RunningOverOrder] as [Calculated_RunningOverOrder],
	[MiddleDetail].[Calculated_CompleteOrderAmt] as [Calculated_CompleteOrderAmt],
	[MiddleDetail].[Calculated_RunRunOver] as [Calculated_RunRunOver],
	[MiddleDetail].[TranGLC_TransAmt] as [TranGLC_TransAmt],
	[MiddleDetail].[InvcDtl1_DocExtPrice] as [InvcDtl1_DocExtPrice],
	(Sum(MiddleDetail.Calculated_RunRunOver) OVER (PARTITION  BY MiddleDetail.Calculated_OrderLineRel ORDER BY MiddleDetail.Calculated_RowNum)) as [Calculated_RunRunRunOver],
	(case 
 when RunRunRunOver = 0 then 0 
 when RunRunRunOver = 1 then MiddleDetail.Calculated_RunningTotal
 else MiddleDetail.InvcDtl1_DocExtPrice
 end) as [Calculated_AdjCompleteOrderTotal],
	(datepart(year,MiddleDetail.Calculated_TranDateEOM)) as [Calculated_Year],
	(datepart(month,MiddleDetail.Calculated_TranDateEOM)) as [Calculated_Month],
	[OrderGLAccnt1].[GLCntrlAcct_GLAccount] as [GLCntrlAcct_GLAccount],
	[OrderGLAccnt1].[GLAccount_AccountDesc] as [GLAccount_AccountDesc]
from  (select 
	[InvTran1].[InvcDtl1_OrderNum] as [InvcDtl1_OrderNum],
	[InvTran1].[InvcDtl1_OrderLine] as [InvcDtl1_OrderLine],
	[InvTran1].[InvcDtl1_OrderRelNum] as [InvcDtl1_OrderRelNum],
	[InvTran1].[Calculated_TranDateEOM] as [Calculated_TranDateEOM],
	[InvTran1].[Calculated_RowNum] as [Calculated_RowNum],
	[InvTran1].[Calculated_OrderLineRel] as [Calculated_OrderLineRel],
	[InvTran1].[Calculated_RunningTotal] as [Calculated_RunningTotal],
	[InvTran1].[OrderDtl_DocUnitPrice] as [OrderDtl_DocUnitPrice],
	[InvTran1].[Calculated_RunningOverOrder] as [Calculated_RunningOverOrder],
	[InvTran1].[Calculated_CompleteOrderAmt] as [Calculated_CompleteOrderAmt],
	(Max(InvTran1.Calculated_RunningOverOrder) OVER(PARTITION BY InvTran1.Calculated_OrderLineRel ORDER BY InvTran1.Calculated_RowNum)) as [Calculated_RunRunOver],
	[InvTran1].[TranGLC_TransAmt] as [TranGLC_TransAmt],
	[InvTran1].[InvcDtl1_DocExtPrice] as [InvcDtl1_DocExtPrice]
from  (select 
	[InvcDtl1].[OrderNum] as [InvcDtl1_OrderNum],
	[InvcDtl1].[OrderLine] as [InvcDtl1_OrderLine],
	[InvcDtl1].[OrderRelNum] as [InvcDtl1_OrderRelNum],
	(EOMONTH(TranGLC.TranDate)) as [Calculated_TranDateEOM],
	(ROW_NUMBER() OVER (PARTITION BY InvcDtl1.Company ORDER BY InvcHead1.ApplyDate)) as [Calculated_RowNum],
	(InvcDtl1.OrderNum * 10000 + InvcDtl1.OrderLine * 100 + InvcDtl1.OrderRelNum) as [Calculated_OrderLineRel],
	(Sum(InvcDtl1.DocExtPrice) OVER (PARTITION BY OrderLineRel ORDER BY InvcHead1.ApplyDate, TranGLC.SysRevID)) as [Calculated_RunningTotal],
	[OrderDtl].[DocUnitPrice] as [OrderDtl_DocUnitPrice],
	(case
 when RunningTotal >= OrderDtl.DocUnitPrice then 1
 else 0
 end) as [Calculated_RunningOverOrder],
	(case 
 when RunningTotal >= OrderDtl.DocUnitPrice then RunningTotal 
 else 0 
 end) as [Calculated_CompleteOrderAmt],
	[TranGLC].[TransAmt] as [TranGLC_TransAmt],
	[InvcDtl1].[DocExtPrice] as [InvcDtl1_DocExtPrice]
from Erp.TranGLC as TranGLC
left outer join Erp.InvcDtl as InvcDtl1 on 
	TranGLC.Company = InvcDtl1.Company
	and TranGLC.Key1 = InvcDtl1.InvoiceNum
	and TranGLC.Key2 = InvcDtl1.InvoiceLine
left outer join Erp.OrderDtl as OrderDtl on 
	InvcDtl1.Company = OrderDtl.Company
	and InvcDtl1.OrderNum = OrderDtl.OrderNum
	and InvcDtl1.OrderLine = OrderDtl.OrderLine
inner join Erp.InvcHead as InvcHead1 on 
	InvcDtl1.Company = InvcHead1.Company
	and InvcDtl1.InvoiceNum = InvcHead1.InvoiceNum
	and ( InvcHead1.InvoiceType = 'ADV'  )

where (TranGLC.RelatedToFile = 'InvcDtl'  and TranGLC.GLAcctContext = 'Deferred Revenue'))  as InvTran1)  as MiddleDetail
left outer join  (select 
	[OrderRel].[OrderNum] as [OrderRel_OrderNum],
	[OrderRel].[OrderLine] as [OrderRel_OrderLine],
	[OrderRel].[OrderRelNum] as [OrderRel_OrderRelNum],
	[GLCntrlAcct].[GLAccount] as [GLCntrlAcct_GLAccount],
	[GLAccount].[AccountDesc] as [GLAccount_AccountDesc]
from Erp.OrderRel as OrderRel
inner join Erp.OrderDtl as OrderDtl1 on 
	OrderDtl1.Company = OrderRel.Company
	and OrderDtl1.OrderNum = OrderRel.OrderNum
	and OrderDtl1.OrderLine = OrderRel.OrderLine
inner join Erp.ProdGrup as ProdGrup on 
	ProdGrup.Company = OrderDtl1.Company
	and ProdGrup.ProdCode = OrderDtl1.ProdCode
inner join Erp.EntityGLC as EntityGLC on 
	ProdGrup.Company = EntityGLC.Company
	and ProdGrup.ProdCode = EntityGLC.Key1
	and ( EntityGLC.GLControlType = 'Product Group'  )

inner join Erp.GLCntrl as GLCntrl on 
	EntityGLC.Company = GLCntrl.Company
	and EntityGLC.GLControlType = GLCntrl.GLControlType
	and EntityGLC.GLControlCode = GLCntrl.GLControlCode
inner join Erp.GLCntrlAcct as GLCntrlAcct on 
	GLCntrl.Company = GLCntrlAcct.Company
	and GLCntrl.GLControlType = GLCntrlAcct.GLControlType
	and GLCntrl.GLControlCode = GLCntrlAcct.GLControlCode
	and ( GLCntrlAcct.GLAcctContext = 'Sales'  )

inner join Erp.GLAccount as GLAccount on 
	GLCntrlAcct.Company = GLAccount.Company
	and GLCntrlAcct.COACode = GLAccount.COACode
	and GLCntrlAcct.GLAccount = GLAccount.GLAccount)  as OrderGLAccnt1 on 
	MiddleDetail.InvcDtl1_OrderNum = OrderGLAccnt1.OrderRel_OrderNum
	and MiddleDetail.InvcDtl1_OrderLine = OrderGLAccnt1.OrderRel_OrderLine
	and MiddleDetail.InvcDtl1_OrderRelNum = OrderGLAccnt1.OrderRel_OrderRelNum)  as DefRevAdvBillLine on 
	DefRevAdvBillLine.InvcDtl1_OrderNum = InvcDtl.OrderNum
	and DefRevAdvBillLine.InvcDtl1_OrderLine = InvcDtl.OrderLine
	and DefRevAdvBillLine.InvcDtl1_OrderRelNum = InvcDtl.OrderRelNum
	and ( DefRevAdvBillLine.Calculated_AdjCompleteOrderTotal <> 0  )

left outer join  (select 
	[OrderRel].[OrderNum] as [OrderRel_OrderNum],
	[OrderRel].[OrderLine] as [OrderRel_OrderLine],
	[OrderRel].[OrderRelNum] as [OrderRel_OrderRelNum],
	[GLCntrlAcct].[GLAccount] as [GLCntrlAcct_GLAccount],
	[GLAccount].[AccountDesc] as [GLAccount_AccountDesc]
from Erp.OrderRel as OrderRel
inner join Erp.OrderDtl as OrderDtl1 on 
	OrderDtl1.Company = OrderRel.Company
	and OrderDtl1.OrderNum = OrderRel.OrderNum
	and OrderDtl1.OrderLine = OrderRel.OrderLine
inner join Erp.ProdGrup as ProdGrup on 
	ProdGrup.Company = OrderDtl1.Company
	and ProdGrup.ProdCode = OrderDtl1.ProdCode
inner join Erp.EntityGLC as EntityGLC on 
	ProdGrup.Company = EntityGLC.Company
	and ProdGrup.ProdCode = EntityGLC.Key1
	and ( EntityGLC.GLControlType = 'Product Group'  )

inner join Erp.GLCntrl as GLCntrl on 
	EntityGLC.Company = GLCntrl.Company
	and EntityGLC.GLControlType = GLCntrl.GLControlType
	and EntityGLC.GLControlCode = GLCntrl.GLControlCode
inner join Erp.GLCntrlAcct as GLCntrlAcct on 
	GLCntrl.Company = GLCntrlAcct.Company
	and GLCntrl.GLControlType = GLCntrlAcct.GLControlType
	and GLCntrl.GLControlCode = GLCntrlAcct.GLControlCode
	and ( GLCntrlAcct.GLAcctContext = 'Sales'  )

inner join Erp.GLAccount as GLAccount on 
	GLCntrlAcct.Company = GLAccount.Company
	and GLCntrlAcct.COACode = GLAccount.COACode
	and GLCntrlAcct.GLAccount = GLAccount.GLAccount)  as OrderGLAccnt on 
	InvcDtl.OrderNum = OrderGLAccnt.OrderRel_OrderNum
	and InvcDtl.OrderLine = OrderGLAccnt.OrderRel_OrderLine
	and InvcDtl.OrderRelNum = OrderGLAccnt.OrderRel_OrderRelNum
group by [InvcHead].[ApplyDate],
	[InvcDtl].[InvoiceNum],
	[InvcDtl].[InvoiceLine],
	[InvcDtl].[OrderNum],
	[InvcDtl].[OrderLine],
	[InvcDtl].[OrderRelNum],
	[OrderGLAccnt].[GLCntrlAcct_GLAccount],
	[OrderGLAccnt].[GLAccount_AccountDesc])  as ShipInvPrevComplete
group by [ShipInvPrevComplete].[Calculated_Year],
	[ShipInvPrevComplete].[Calculated_Month],
	[ShipInvPrevComplete].[GLCntrlAcct_GLAccount],
	[ShipInvPrevComplete].[GLAccount_AccountDesc])  as PB_ShipPrevAdvInv on 
	GLPeriodBal.FiscalYear = PB_ShipPrevAdvInv.Calculated_Year
	and GLPeriodBal.FiscalPeriod = PB_ShipPrevAdvInv.Calculated_Month
	and GLPeriodBal.BalanceAcct = PB_ShipPrevAdvInv.GLCntrlAcct_GLAccount
where (GLPeriodBal.SegValue1 like '4'  and GLPeriodBal.FiscalPeriod <> 0  and GLPeriodBal.FiscalYear = 2019)

[knash (Ken Nash)]

NICE!!!

When did it get slow?

I see there are a bunch of joins happening. Did you try and run this in TOAD or some sort of SQL Editor? Check the execution Plan see if there are indexes you can use and then add those fields.

select 
	[GLPeriodBal].[BalanceAcct] as [GLPeriodBal_BalanceAcct],
	[GLPeriodBal].[FiscalYear] as [GLPeriodBal_FiscalYear],
	[GLPeriodBal].[FiscalPeriod] as [GLPeriodBal_FiscalPeriod],
	[PB_ShipPrevAdvInv].[GLAccount_AccountDesc] as [GLAccount_AccountDesc],
	[PB_ShipPrevAdvInv].[Calculated_ShipCurrentInvPast] as [Calculated_ShipCurrentInvPast]
from Erp.GLPeriodBal as GLPeriodBal

left outer join  
  
  (select 
	[ShipInvPrevComplete].[Calculated_Year] as [Calculated_Year],
	[ShipInvPrevComplete].[Calculated_Month] as [Calculated_Month],
	[ShipInvPrevComplete].[GLCntrlAcct_GLAccount] as [GLCntrlAcct_GLAccount],
	[ShipInvPrevComplete].[GLAccount_AccountDesc] as [GLAccount_AccountDesc],
	(Sum(ShipInvPrevComplete.Calculated_ReleasedPrevCompAdvBill)) as [Calculated_ShipCurrentInvPast]
from  (select 
	[InvcHead].[ApplyDate] as [InvcHead_ApplyDate],
	[InvcDtl].[InvoiceNum] as [InvcDtl_InvoiceNum],
	[InvcDtl].[InvoiceLine] as [InvcDtl_InvoiceLine],
	[InvcDtl].[OrderNum] as [InvcDtl_OrderNum],
	[InvcDtl].[OrderLine] as [InvcDtl_OrderLine],
	[InvcDtl].[OrderRelNum] as [InvcDtl_OrderRelNum],
	(Max(InvcDtl.ExtPrice)) as [Calculated_ExtPrice],
	(Max(InvcDtl.DocAdvanceBillCredit)) as [Calculated_AdvBillCredit],
	(sum(case
 when DefRevAdvBillLine.Calculated_TranDateEOM <= EOMONTH(InvcHead.ApplyDate) then DefRevAdvBillLine.Calculated_AdjCompleteOrderTotal
 else 0
 end)) as [Calculated_PrevCompleteAdvBill],
	(case
 when (Max(InvcDtl.DocAdvanceBillCredit)) = 0 then 0
 when 	(sum(case
 when DefRevAdvBillLine.Calculated_TranDateEOM <= EOMONTH(InvcHead.ApplyDate) then DefRevAdvBillLine.Calculated_AdjCompleteOrderTotal
 else 0
 end)) = 0 then 0
 when 	(sum(case
 when DefRevAdvBillLine.Calculated_TranDateEOM <= EOMONTH(InvcHead.ApplyDate) then DefRevAdvBillLine.Calculated_AdjCompleteOrderTotal
 else 0
 end)) > (Max(InvcDtl.DocAdvanceBillCredit)) then (Max(InvcDtl.DocAdvanceBillCredit))
 else 	(sum(case
 when DefRevAdvBillLine.Calculated_TranDateEOM <= EOMONTH(InvcHead.ApplyDate) then DefRevAdvBillLine.Calculated_AdjCompleteOrderTotal
 else 0
 end))
 end) as [Calculated_ReleasedPrevCompAdvBill],
	[OrderGLAccnt].[GLCntrlAcct_GLAccount] as [GLCntrlAcct_GLAccount],
	[OrderGLAccnt].[GLAccount_AccountDesc] as [GLAccount_AccountDesc],
	(datepart(year,Max(InvcHead.ApplyDate))) as [Calculated_Year],
	(datepart(month,Max(InvcHead.ApplyDate))) as [Calculated_Month]
from Erp.InvcDtl as InvcDtl
inner join Erp.InvcHead as InvcHead on 
	InvcDtl.Company = InvcHead.Company
	and InvcDtl.InvoiceNum = InvcHead.InvoiceNum
	and ( InvcHead.InvoiceType <> 'ADV'  )

left outer join  (select 
	[MiddleDetail].[InvcDtl1_OrderNum] as [InvcDtl1_OrderNum],
	[MiddleDetail].[InvcDtl1_OrderLine] as [InvcDtl1_OrderLine],
	[MiddleDetail].[InvcDtl1_OrderRelNum] as [InvcDtl1_OrderRelNum],
	[MiddleDetail].[Calculated_TranDateEOM] as [Calculated_TranDateEOM],
	[MiddleDetail].[Calculated_RowNum] as [Calculated_RowNum],
	[MiddleDetail].[Calculated_OrderLineRel] as [Calculated_OrderLineRel],
	[MiddleDetail].[Calculated_RunningTotal] as [Calculated_RunningTotal],
	[MiddleDetail].[OrderDtl_DocUnitPrice] as [OrderDtl_DocUnitPrice],
	[MiddleDetail].[Calculated_RunningOverOrder] as [Calculated_RunningOverOrder],
	[MiddleDetail].[Calculated_CompleteOrderAmt] as [Calculated_CompleteOrderAmt],
	[MiddleDetail].[Calculated_RunRunOver] as [Calculated_RunRunOver],
	[MiddleDetail].[TranGLC_TransAmt] as [TranGLC_TransAmt],
	[MiddleDetail].[InvcDtl1_DocExtPrice] as [InvcDtl1_DocExtPrice],
	(Sum(MiddleDetail.Calculated_RunRunOver) OVER (PARTITION  BY MiddleDetail.Calculated_OrderLineRel ORDER BY MiddleDetail.Calculated_RowNum)) as [Calculated_RunRunRunOver],
	(case 
 when MiddleDetail.Calculated_RunRunOver = 0 then 0 
 when MiddleDetail.Calculated_RunRunOver = 1 then MiddleDetail.Calculated_RunningTotal
 else MiddleDetail.InvcDtl1_DocExtPrice
 end) as [Calculated_AdjCompleteOrderTotal],
	(datepart(year,MiddleDetail.Calculated_TranDateEOM)) as [Calculated_Year],
	(datepart(month,MiddleDetail.Calculated_TranDateEOM)) as [Calculated_Month],
	[OrderGLAccnt1].[GLCntrlAcct_GLAccount] as [GLCntrlAcct_GLAccount],
	[OrderGLAccnt1].[GLAccount_AccountDesc] as [GLAccount_AccountDesc]
from  (select 
	[InvTran1].[InvcDtl1_OrderNum] as [InvcDtl1_OrderNum],
	[InvTran1].[InvcDtl1_OrderLine] as [InvcDtl1_OrderLine],
	[InvTran1].[InvcDtl1_OrderRelNum] as [InvcDtl1_OrderRelNum],
	[InvTran1].[Calculated_TranDateEOM] as [Calculated_TranDateEOM],
	[InvTran1].[Calculated_RowNum] as [Calculated_RowNum],
	[InvTran1].[Calculated_OrderLineRel] as [Calculated_OrderLineRel],
	[InvTran1].[Calculated_RunningTotal] as [Calculated_RunningTotal],
	[InvTran1].[OrderDtl_DocUnitPrice] as [OrderDtl_DocUnitPrice],
	[InvTran1].[Calculated_RunningOverOrder] as [Calculated_RunningOverOrder],
	[InvTran1].[Calculated_CompleteOrderAmt] as [Calculated_CompleteOrderAmt],
	(Max(InvTran1.Calculated_RunningOverOrder) OVER(PARTITION BY InvTran1.Calculated_OrderLineRel ORDER BY InvTran1.Calculated_RowNum)) as [Calculated_RunRunOver],
	[InvTran1].[TranGLC_TransAmt] as [TranGLC_TransAmt],
	[InvTran1].[InvcDtl1_DocExtPrice] as [InvcDtl1_DocExtPrice]
from  (select 
	[InvcDtl1].[OrderNum] as [InvcDtl1_OrderNum],
	[InvcDtl1].[OrderLine] as [InvcDtl1_OrderLine],
	[InvcDtl1].[OrderRelNum] as [InvcDtl1_OrderRelNum],
	(EOMONTH(TranGLC.TranDate)) as [Calculated_TranDateEOM],
	(ROW_NUMBER() OVER (PARTITION BY InvcDtl1.Company ORDER BY InvcHead1.ApplyDate)) as [Calculated_RowNum],
	(InvcDtl1.OrderNum * 10000 + InvcDtl1.OrderLine * 100 + InvcDtl1.OrderRelNum) as [Calculated_OrderLineRel],
	(Sum(InvcDtl1.DocExtPrice) OVER (PARTITION BY (InvcDtl1.OrderNum * 10000 + InvcDtl1.OrderLine * 100 + InvcDtl1.OrderRelNum) ORDER BY InvcHead1.ApplyDate, TranGLC.SysRevID)) as [Calculated_RunningTotal],
	[OrderDtl].[DocUnitPrice] as [OrderDtl_DocUnitPrice],
	(case
 when 	(Sum(InvcDtl1.DocExtPrice) OVER (PARTITION BY (InvcDtl1.OrderNum * 10000 + InvcDtl1.OrderLine * 100 + InvcDtl1.OrderRelNum) ORDER BY InvcHead1.ApplyDate, TranGLC.SysRevID)) >= OrderDtl.DocUnitPrice then 1
 else 0
 end) as [Calculated_RunningOverOrder],
	(case 
 when 	(Sum(InvcDtl1.DocExtPrice) OVER (PARTITION BY (InvcDtl1.OrderNum * 10000 + InvcDtl1.OrderLine * 100 + InvcDtl1.OrderRelNum) ORDER BY InvcHead1.ApplyDate, TranGLC.SysRevID)) >= OrderDtl.DocUnitPrice then 	(Sum(InvcDtl1.DocExtPrice) OVER (PARTITION BY (InvcDtl1.OrderNum * 10000 + InvcDtl1.OrderLine * 100 + InvcDtl1.OrderRelNum) ORDER BY InvcHead1.ApplyDate, TranGLC.SysRevID)) 
 else 0 
 end) as [Calculated_CompleteOrderAmt],
	[TranGLC].[TransAmt] as [TranGLC_TransAmt],
	[InvcDtl1].[DocExtPrice] as [InvcDtl1_DocExtPrice]
from Erp.TranGLC as TranGLC
left outer join Erp.InvcDtl as InvcDtl1 on 
	TranGLC.Company = InvcDtl1.Company
	and TranGLC.Key1 = InvcDtl1.InvoiceNum
	and TranGLC.Key2 = InvcDtl1.InvoiceLine
left outer join Erp.OrderDtl as OrderDtl on 
	InvcDtl1.Company = OrderDtl.Company
	and InvcDtl1.OrderNum = OrderDtl.OrderNum
	and InvcDtl1.OrderLine = OrderDtl.OrderLine
inner join Erp.InvcHead as InvcHead1 on 
	InvcDtl1.Company = InvcHead1.Company
	and InvcDtl1.InvoiceNum = InvcHead1.InvoiceNum
	and ( InvcHead1.InvoiceType = 'ADV'  )

where (TranGLC.RelatedToFile = 'InvcDtl'  and TranGLC.GLAcctContext = 'Deferred Revenue'))  as InvTran1)  as MiddleDetail
left outer join  (select 
	[OrderRel].[OrderNum] as [OrderRel_OrderNum],
	[OrderRel].[OrderLine] as [OrderRel_OrderLine],
	[OrderRel].[OrderRelNum] as [OrderRel_OrderRelNum],
	[GLCntrlAcct].[GLAccount] as [GLCntrlAcct_GLAccount],
	[GLAccount].[AccountDesc] as [GLAccount_AccountDesc]
from Erp.OrderRel as OrderRel
inner join Erp.OrderDtl as OrderDtl1 on 
	OrderDtl1.Company = OrderRel.Company
	and OrderDtl1.OrderNum = OrderRel.OrderNum
	and OrderDtl1.OrderLine = OrderRel.OrderLine
inner join Erp.ProdGrup as ProdGrup on 
	ProdGrup.Company = OrderDtl1.Company
	and ProdGrup.ProdCode = OrderDtl1.ProdCode
inner join Erp.EntityGLC as EntityGLC on 
	ProdGrup.Company = EntityGLC.Company
	and ProdGrup.ProdCode = EntityGLC.Key1
	and ( EntityGLC.GLControlType = 'Product Group'  )

inner join Erp.GLCntrl as GLCntrl on 
	EntityGLC.Company = GLCntrl.Company
	and EntityGLC.GLControlType = GLCntrl.GLControlType
	and EntityGLC.GLControlCode = GLCntrl.GLControlCode
inner join Erp.GLCntrlAcct as GLCntrlAcct on 
	GLCntrl.Company = GLCntrlAcct.Company
	and GLCntrl.GLControlType = GLCntrlAcct.GLControlType
	and GLCntrl.GLControlCode = GLCntrlAcct.GLControlCode
	and ( GLCntrlAcct.GLAcctContext = 'Sales'  )

inner join Erp.GLAccount as GLAccount on 
	GLCntrlAcct.Company = GLAccount.Company
	and GLCntrlAcct.COACode = GLAccount.COACode
	and GLCntrlAcct.GLAccount = GLAccount.GLAccount)  as OrderGLAccnt1 on 
	MiddleDetail.InvcDtl1_OrderNum = OrderGLAccnt1.OrderRel_OrderNum
	and MiddleDetail.InvcDtl1_OrderLine = OrderGLAccnt1.OrderRel_OrderLine
	and MiddleDetail.InvcDtl1_OrderRelNum = OrderGLAccnt1.OrderRel_OrderRelNum)  as DefRevAdvBillLine on 
	DefRevAdvBillLine.InvcDtl1_OrderNum = InvcDtl.OrderNum
	and DefRevAdvBillLine.InvcDtl1_OrderLine = InvcDtl.OrderLine
	and DefRevAdvBillLine.InvcDtl1_OrderRelNum = InvcDtl.OrderRelNum
	and ( DefRevAdvBillLine.Calculated_AdjCompleteOrderTotal <> 0  )

left outer join  (select 
	[OrderRel].[OrderNum] as [OrderRel_OrderNum],
	[OrderRel].[OrderLine] as [OrderRel_OrderLine],
	[OrderRel].[OrderRelNum] as [OrderRel_OrderRelNum],
	[GLCntrlAcct].[GLAccount] as [GLCntrlAcct_GLAccount],
	[GLAccount].[AccountDesc] as [GLAccount_AccountDesc]
from Erp.OrderRel as OrderRel
inner join Erp.OrderDtl as OrderDtl1 on 
	OrderDtl1.Company = OrderRel.Company
	and OrderDtl1.OrderNum = OrderRel.OrderNum
	and OrderDtl1.OrderLine = OrderRel.OrderLine
inner join Erp.ProdGrup as ProdGrup on 
	ProdGrup.Company = OrderDtl1.Company
	and ProdGrup.ProdCode = OrderDtl1.ProdCode
inner join Erp.EntityGLC as EntityGLC on 
	ProdGrup.Company = EntityGLC.Company
	and ProdGrup.ProdCode = EntityGLC.Key1
	and ( EntityGLC.GLControlType = 'Product Group'  )

inner join Erp.GLCntrl as GLCntrl on 
	EntityGLC.Company = GLCntrl.Company
	and EntityGLC.GLControlType = GLCntrl.GLControlType
	and EntityGLC.GLControlCode = GLCntrl.GLControlCode
inner join Erp.GLCntrlAcct as GLCntrlAcct on 
	GLCntrl.Company = GLCntrlAcct.Company
	and GLCntrl.GLControlType = GLCntrlAcct.GLControlType
	and GLCntrl.GLControlCode = GLCntrlAcct.GLControlCode
	and ( GLCntrlAcct.GLAcctContext = 'Sales'  )

inner join Erp.GLAccount as GLAccount on 
	GLCntrlAcct.Company = GLAccount.Company
	and GLCntrlAcct.COACode = GLAccount.COACode
	and GLCntrlAcct.GLAccount = GLAccount.GLAccount)  as OrderGLAccnt on 
	InvcDtl.OrderNum = OrderGLAccnt.OrderRel_OrderNum
	and InvcDtl.OrderLine = OrderGLAccnt.OrderRel_OrderLine
	and InvcDtl.OrderRelNum = OrderGLAccnt.OrderRel_OrderRelNum
group by [InvcHead].[ApplyDate],
	[InvcDtl].[InvoiceNum],
	[InvcDtl].[InvoiceLine],
	[InvcDtl].[OrderNum],
	[InvcDtl].[OrderLine],
	[InvcDtl].[OrderRelNum],
	[OrderGLAccnt].[GLCntrlAcct_GLAccount],
	[OrderGLAccnt].[GLAccount_AccountDesc])  as ShipInvPrevComplete
group by [ShipInvPrevComplete].[Calculated_Year],
	[ShipInvPrevComplete].[Calculated_Month],
	[ShipInvPrevComplete].[GLCntrlAcct_GLAccount],
	[ShipInvPrevComplete].[GLAccount_AccountDesc])  as PB_ShipPrevAdvInv on 
	GLPeriodBal.FiscalYear = PB_ShipPrevAdvInv.Calculated_Year
	and GLPeriodBal.FiscalPeriod = PB_ShipPrevAdvInv.Calculated_Month
	and GLPeriodBal.BalanceAcct = PB_ShipPrevAdvInv.GLCntrlAcct_GLAccount

where (GLPeriodBal.SegValue1 like '4'  and GLPeriodBal.FiscalPeriod <> 0  and GLPeriodBal.FiscalYear = 2019)

[Mike (Mike Watkinson)]

@knash I have not done run it through a SQL editor or checked the execution path. I will look into that, never done that before. As for when it got slow, this is still a new one I am working on so it is in process. That said, I ran the only sub-query I have brought into the top level so far by itself and it was ~400ms. Then when I brought that into the top level and linked to one table it got stuck.

I added company to all of my joins as @Banderson suggested and with that and filtering the table I am joining the sub-query to down to like 10 records I was able to get it to run in 40,000ms. If I opened the table up like I am needing to do, then it will time out.

[skearney (Shannon Kearney)]

How do you adjust the timeout?

[Randy (Randy Stulce)]

In the BAQ designer under Actions - Execution Settings. You’ll need to click on the new icon then use the pulldown under Setting Name. NOTE: Use caution adjusting setting in here and always test on a non-production server.

[Banderson (Brandon Anderson)]

If the query cannot be changed anymore (like adding all of the indexed fields you can), getting the SQL execution plan and opening it up in the a sql editor will give you a suggestion to add an index to the database. The thread that I linked below is one that I did that too and increased the performance immensely. Keep in mind, you don’t want to go crazy on adding indexes to the database, but if you have something you really need, it can make a query that’s unusable, usable.

Unions and performance

[Mark_Wonsil]

@Banderson

Keep in mind, you don’t want to go crazy on adding indexes to the database,

Also, make sure you’re using the indexes that Epicor already provides by including as many fields in the join as possible from left to right (company is almost always the first field in an index). I have found this tip (thanks to whomever that was) has worked especially well on PartTran. Indexes are shown in the Data Dictionary viewer or Haso’s CHM file.

Mark W.