Can you force a query execution plan for BAQ

Kinetic ERP
1

I’m wondering if I can force a specific execution plan for a BAQ. I’m not entirely sure that’s the solution, but I’m trying to troubleshoot an issue where I can run a BAQ and it loads fairly quickly. But another user can’t and it times out. Any ideas where to go from here? For what it’s worth, I also tried copying the BAQ to a new name in hopes it might re-build the BAQ execution plan from scratch. But that didn’t work. I still have similar good performance and my other user can’t get results due to timeout.

Thanks in advance!

2

I don’t think you can force a plan, but you could add the QueryOption RECOMPILE in the execution settings, and persist it.

It’ll try to make a new plan every time.

1 Like
3

Well, now I can’t execute it :frowning:
It times out for me also haha.

Any other thing you can think of that might cause a BAQ to work fine for one user but timeout for another?

1 Like
4

Karma?

1 Like
5

Could Territory or Field security be at play?

6

Maybe? Is this how it would manifest itself? I would’ve thought it might just come back with 0 rows or something. But I’m querying transaction data so I wouldn’t think Territory would in play.

7

Do you have the customer table in the query?

8

PartTran data? Is one user using a time frame and the other trying to return all records? Can you post the SQL of the BAQ?

9

No customer references. It’s all about cost/running qty on hand.

10

Usually it’s the customer table that brings in the security data.

Do you know how to catch the SQL that’s being run on the server? Do you have access to your database? If you can do that, you can tell if it’s a security application or not.

Other than option (recompile), you could also try option (optimize for unknown) which will make it so that it doesn’t get stuck on thinking that it’s looking for a very specific small dataset.

2 Likes
11

or for specific parameter values

2 Likes
12

Maybe a little X → Y going on here. You have a slow BAQ and assumed that it was an execution plan and asked how to force one. How did you eliminate other sources of slowness? Are you sure it’s the execution plan? Would you mind pasting the SQL? That may provide more context for those who are trying to help.

1 Like
13

It could definitely be an X - Y thing.
Here’s the query pasted from the BAQ. Essentially, my goal is to generate a Stock Status type report where the user enters a cutoff date and the BAQ calculates the qty on hand and value as of that date. There may be ways to optimize the SQL if needed. However, I didn’t feel it was needed because it ran just fine (for me). But I digress. Here it is:

/*
 * 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.
 */
 
with [PartLotTrans] as 
(select 
	(sum(1) over (partition by PartTran.Company, PartFIFOTran.PartNum, PartTran.Plant, PartTran.LotNum order by PartFIFOTran.TranNum asc, PartFIFOTran.TranSeq asc)) as [Calculated_Ind],
	[PartFIFOTran].[Company] as [PartFIFOTran_Company],
	[PartFIFOTran].[PartNum] as [PartFIFOTran_PartNum],
	[PartFIFOTran].[LotNum] as [PartFIFOTran_LotNum],
	[PartTran].[Plant] as [PartTran_Plant],
	(case
   when POInfo.Calculated_OrigRecDate is not null
   then POInfo.Calculated_OrigRecDate
   else PartTran.TranDate
 end) as [Calculated_TranDate],
	(case
   when PartFIFOTran.TranQty is not null
   then PartFIFOTran.TranQty
   else PartTran.ActTranQty
 end) as [Calculated_TranQty],
	(sum(TranQty) over (partition by PartTran.Company, PartTran.PartNum, PartTran.Plant, PartTran.LotNum order by PartTran.TranNum, PartFIFOTran.TranSeq asc)) as [Calculated_RunningTotal],
	(case
   when RunningTotal <> 0
   then InvValue / RunningTotal
   else 0
 end) as [Calculated_UnitCost],
	(sum(ExtCost) over (partition by PartTran.Company, PartTran.PartNum, PartTran.Plant, PartTran.LotNum order by PartTran.TranNum, PartFIFOTran.TranSeq asc)) as [Calculated_InvValue],
	(case
   when PartFIFOTran.TranType = 'ADJ-PUR'
   then 
     case
       when PartFIFOTran.TranQty <> 0
       then PartFIFOTran.ExtCost
       else PartTran.ExtCost
     end
   when PartFIFOTran.ExtCost is not null
   then PartFIFOTran.ExtCost
   else PartTran.ExtCost
 end) as [Calculated_ExtCost],
	(case
   when PartFIFOTran.TranType is not null
   then PartFIFOTran.TranType
   else PartTran.TranType
 end) as [Calculated_TranType],
	[PartFIFOTran].[TranNum] as [PartFIFOTran_TranNum],
	[PartFIFOTran].[TranSeq] as [PartFIFOTran_TranSeq],
	[PartFIFOTran].[TranQty] as [PartFIFOTran_TranQty],
	[POInfo].[Calculated_OrigRecDate] as [Calculated_OrigRecDate],
	[POInfo].[Calculated_RevRecDate] as [Calculated_RevRecDate],
	[POInfo].[Calculated_NumReceipts] as [Calculated_NumReceipts],
	(case
   when PartFIFOTran.TranType = 'ADJ-PUR'
   then 
     case
       when PartFIFOTran.TranQty <> 0
       then PartFIFOTran.MtlUnitCost * PartFIFOTran.TranQty
       else PartTran.MtlUnitCost
     end
   when PartFIFOTran.MtlUnitCost is not null
   then PartFIFOTran.MtlUnitCost * PartFIFOTran.TranQty
   else PartTran.MtlUnitCost * PartTran.ActTranQty
 end) as [Calculated_MtlCost],
	(case
   when PartFIFOTran.TranType = 'ADJ-PUR'
   then 
     case
       when PartFIFOTran.TranQty <> 0
       then PartFIFOTran.MtlBurUnitCost * PartFIFOTran.TranQty
       else PartTran.MtlBurUnitCost
     end
   when PartFIFOTran.MtlBurUnitCost is not null
   then PartFIFOTran.MtlBurUnitCost * PartFIFOTran.TranQty
   else PartTran.MtlBurUnitCost * PartTran.ActTranQty
 end) as [Calculated_MtlBurCost],
	(case
   when PartFIFOTran.TranType = 'ADJ-PUR'
   then 
     case
       when PartFIFOTran.TranQty <> 0
       then PartFIFOTran.SubUnitCost * PartFIFOTran.TranQty
       else PartTran.SubUnitCost
     end
   when PartFIFOTran.SubUnitCost is not null
   then PartFIFOTran.SubUnitCost * PartFIFOTran.TranQty
   else PartTran.SubUnitCost * PartTran.ActTranQty
 end) as [Calculated_SubCost],
	(case
   when RunningTotal <> 0
   then RunningMtlCost / RunningTotal
   else 0
 end) as [Calculated_MtlUnitCost],
	(case
   when RunningTotal <> 0
   then RunningMtlBurCost / RunningTotal
   else 0
 end) as [Calculated_MtlBurUnitCost],
	(case
   when RunningTotal <> 0
   then RunningSubCost / RunningTotal
   else 0
 end) as [Calculated_SubUnitCost],
	(sum(MtlCost) over (partition by PartTran.Company, PartTran.PartNum, PartTran.Plant, PartTran.LotNum order by PartFIFOTran.TranNum asc, PartFIFOTran.TranSeq asc)) as [Calculated_RunningMtlCost],
	(sum(MtlBurCost) over (partition by PartTran.Company, PartTran.PartNum, PartTran.Plant, PartTran.LotNum order by PartFIFOTran.TranNum asc, PartFIFOTran.TranSeq asc)) as [Calculated_RunningMtlBurCost],
	(sum(SubCost) over (partition by PartTran.Company, PartTran.PartNum, PartTran.Plant, PartTran.LotNum order by PartFIFOTran.TranNum asc, PartFIFOTran.TranSeq asc)) as [Calculated_RunningSubCost],
	(case
   when InvValue < 0
   then 1
   else 0
 end) as [Calculated_IsNegativeCost],
	[PartTran].[WareHouseCode] as [PartTran_WareHouseCode]
from Erp.PartFIFOTran as PartFIFOTran
inner join Erp.PartTran as PartTran on 
	PartFIFOTran.Company = PartTran.Company
	and PartFIFOTran.TranNum = PartTran.TranNum
left outer join  (select 
	[PartTran1].[Company] as [PartTran1_Company],
	[PartTran1].[PartNum] as [PartTran1_PartNum],
	[PartTran1].[PONum] as [PartTran1_PONum],
	[PartTran1].[POLine] as [PartTran1_POLine],
	[PartTran1].[PORelNum] as [PartTran1_PORelNum],
	(min(PartTran1.TranDate)) as [Calculated_OrigRecDate],
	(max(PartTran1.TranDate)) as [Calculated_RevRecDate],
	(sum(
   case
     when PartTran1.TranQty > 0
     then 1
     else 0
   end
 )) as [Calculated_NumReceipts]
from Erp.PartTran as PartTran1
where (PartTran1.TranType = 'PUR-STK')
group by [PartTran1].[Company],
	[PartTran1].[PartNum],
	[PartTran1].[PONum],
	[PartTran1].[POLine],
	[PartTran1].[PORelNum])  as POInfo on 
	PartTran.Company = POInfo.PartTran1_Company
	and PartTran.PONum = POInfo.PartTran1_PONum
	and PartTran.POLine = POInfo.PartTran1_POLine
	and PartTran.PORelNum = POInfo.PartTran1_PORelNum
	and PartTran.PartNum = POInfo.PartTran1_PartNum
	and ( POInfo.Calculated_NumReceipts > 1  )

inner join Erp.PartPlant as PartPlant1 on 
	PartTran.Company = PartPlant1.Company
	and PartTran.PartNum = PartPlant1.PartNum
	and PartTran.Plant = PartPlant1.Plant
	and ( PartPlant1.NonStock = 0  )

where ((case
   when POInfo.Calculated_OrigRecDate is not null
   then POInfo.Calculated_OrigRecDate
   else PartTran.TranDate
 end) <= @CutoffDate))

select 
	[PartLotTrans1].[PartFIFOTran_Company] as [PartFIFOTran_Company],
	[Part].[PartDescription] as [Part_PartDescription],
	[Part].[ClassID] as [Part_ClassID],
	[PartLotTrans1].[Calculated_TranDate] as [Calculated_TranDate],
	[PartLotTrans1].[Calculated_TranType] as [Calculated_TranType],
	[PartLotTrans1].[PartTran_Plant] as [PartTran_Plant],
	[PartLotTrans1].[PartFIFOTran_PartNum] as [PartFIFOTran_PartNum],
	[PartLotTrans1].[PartFIFOTran_LotNum] as [PartFIFOTran_LotNum],
	[PartLotTrans1].[Calculated_RunningTotal] as [Calculated_RunningTotal],
	[Part].[IUM] as [Part_IUM],
	[PartLotTrans1].[Calculated_MtlUnitCost] as [Calculated_MtlUnitCost],
	[PartLotTrans1].[Calculated_MtlBurUnitCost] as [Calculated_MtlBurUnitCost],
	[PartLotTrans1].[Calculated_SubUnitCost] as [Calculated_SubUnitCost],
	[PartLotTrans1].[Calculated_UnitCost] as [Calculated_UnitCost],
	[PartLotTrans1].[Calculated_InvValue] as [Calculated_InvValue]
from  PartLotTrans  as PartLotTrans1
inner join  (select 
	[PartLotTrans].[PartFIFOTran_Company] as [PartFIFOTran_Company],
	[PartLotTrans].[PartFIFOTran_PartNum] as [PartFIFOTran_PartNum],
	[PartLotTrans].[PartFIFOTran_LotNum] as [PartFIFOTran_LotNum],
	[PartLotTrans].[PartTran_Plant] as [PartTran_Plant],
	(max(PartLotTrans.Calculated_Ind)) as [Calculated_MaxIndex]
from  PartLotTrans  as PartLotTrans
group by [PartLotTrans].[PartFIFOTran_Company],
	[PartLotTrans].[PartFIFOTran_PartNum],
	[PartLotTrans].[PartFIFOTran_LotNum],
	[PartLotTrans].[PartTran_Plant])  as GetMaxIndex on 
	PartLotTrans1.PartFIFOTran_Company = GetMaxIndex.PartFIFOTran_Company
	and PartLotTrans1.PartTran_Plant = GetMaxIndex.PartTran_Plant
	and PartLotTrans1.PartFIFOTran_PartNum = GetMaxIndex.PartFIFOTran_PartNum
	and PartLotTrans1.PartFIFOTran_LotNum = GetMaxIndex.PartFIFOTran_LotNum
	and PartLotTrans1.Calculated_Ind = GetMaxIndex.Calculated_MaxIndex
inner join Erp.Part as Part on 
	PartLotTrans1.PartFIFOTran_Company = Part.Company
	and PartLotTrans1.PartFIFOTran_PartNum = Part.PartNum
	and ( not Part.ClassID = 'SAMP'  and not Part.ClassID = 'INFR'  and not Part.ClassID = 'WASE'  and not Part.ClassID = 'FDEV'  and not Part.ClassID = 'OUFR'  and not Part.ClassID = 'STOR'  and not Part.ClassID = 'TAMT'  and not Part.ClassID = 'RCLE'  and not Part.ClassID = 'RCRM'  and not Part.ClassID = 'TRLE'  )

left outer join  (select 
	[PartTran2].[Company] as [PartTran2_Company],
	[PartTran2].[PartNum] as [PartTran2_PartNum],
	[PartTran2].[Plant] as [PartTran2_Plant],
	[PartTran2].[LotNum] as [PartTran2_LotNum],
	(min(PartTran2.TranDate)) as [Calculated_MinTranDate]
from Erp.PartTran as PartTran2
left outer join Erp.PartFIFOTran as PartFIFOTran1 on 
	PartTran2.Company = PartFIFOTran1.Company
	and PartTran2.TranNum = PartFIFOTran1.TranNum
where (PartFIFOTran1.TranNum is null)

group by [PartTran2].[Company],
	[PartTran2].[PartNum],
	[PartTran2].[Plant],
	[PartTran2].[LotNum])  as SubQuery5 on 
	PartLotTrans1.PartFIFOTran_PartNum = SubQuery5.PartTran2_PartNum
	and PartLotTrans1.PartFIFOTran_Company = SubQuery5.PartTran2_Company
	and PartLotTrans1.PartTran_Plant = SubQuery5.PartTran2_Plant
	and PartLotTrans1.PartFIFOTran_LotNum = SubQuery5.PartTran2_LotNum
	and ( SubQuery5.Calculated_MinTranDate <= @CutoffDate  )

where (PartLotTrans1.Calculated_RunningTotal > 0)
order by PartLotTrans1.PartTran_Plant, PartLotTrans1.PartFIFOTran_PartNum, PartLotTrans1.PartTran_WareHouseCode, PartLotTrans1.PartFIFOTran_LotNum
14

Daniel. This is only one of the most complicated BAQs that people ever try to do, and you’re trying to do it using FIFO costing! I’m not surprised that it times out. I’m more surprised that it finishes at all!

How often do they want to run this and what do they use it for? Is it only for month-end dates when they fail to do a regular Stock Status report? Do they always need for all parts or can they cut down the number of records?

If it’s a month end thing, others have captured the costs at that time and saved them to a UD Table. This would simplify your BAQ quite a bit and make it more performant.

3 Likes
15

Stock Status only prints one site at a time… that’s my main issue. I wonder how easy would it be to write some kind of special program that combines multiple Excel spreadsheets into one? Or, alternatively, could we trick the report into generating all sites at once? The main issue we’re trying to overcome is the fact there there are many sites to run. I feel like I could set up a process to print all of them effortlessly enough… but you’d still be stuck with a bunch of reports to have to go through. Any ideas how to get that to dump into 1 report with all sites combined? In the past, I’ve automated a BAQ report that ran on the first of the month in the middle of the night which was fairly easy to produce. But in this case, I am being asked to make it flexible to enter an “as of” date like the Stock Status report can do.

1 Like
16

Confused Lou Taylor Pucci GIF by Apple TV+

Just curious what the business case is… Are they trying to graph inventory levels by day? Being a (poor) mathematician, I’d like to solve for X in the X → Y problem.

2 Likes
17

One other thought. The thing that seems to destroy the BAQ is when I try to exclude Non-Stock parts from PartPlant table. When I omit that table from anywhere in the BAQ, it runs really fast. Like 4 seconds. I add that in, timeout. Every time. Every possible way I could think of to filter out Non-Stock. Any other ideas how I might filter out Non-Stock parts beside partplant?

18

You could add an index for non-stock on the part plant table.

A less nuclear option would be to add the filter in the subuery results filter instead of on the table. Then it should filter after the fact. (although sometimes SQL is mean and gets helpful excel style)

3 Likes
19

Stock Status can’t deliver multiple sites at once. There’s also a gap in the Stock Status report where Lots that are consumed between then and now don’t cost correctly when entering a past As Of date. I have a feeling I could fix that on the report though if that were the only issue. The main issue is the multi-sites. But yes, as far as business case, they use it to map the inventory values/quantities by week and month.

1 Like
20

This is not going to sound like an answer, but:

Add a criteria on one or more tables of this:

image
image807×191 10.7 KB

You would not believe how often this magically fixes BAQs.

2 Likes