BAQ Filtering By InvcHead.InvoiceDate

Epicor ERP 10
1

Team,
I have a complicated BAQ that is able to run in 5-6 seconds. The top table is InvcHead. If I add a filter for InvcHead.InvoiceDate >= 1/1/2020 the BAQ fails.
Unfiltered, I get 200K+ records. Filtered, I should get less than 500. Instead I get an error:

Severity: Error, Table: , Field: , RowID: , Text: Bad SQL statement.
Review the server event logs for details.
Query returned 0 row(s).
Query has no more records to return.
Query execution total time: 30090.1305 ms.

All other table filters work fine. The Query Execution Plan says something wildly different (and incorrect if I read it right). It says there is a missing Index that would save 22% off the execution (forgive my lack of SQL admin knowledge).

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [Erp].[InvcDtl] ([ShipToCustNum],[ShipToNum],[Company])
INCLUDE ([InvoiceNum],[InvoiceLine],[PartNum],[LineDesc],[DocUnitPrice],[DocExtPrice],[DocDiscount],[OrderNum],[OrderLine],[MtlUnitCost],[LbrUnitCost],[BurUnitCost],[SubUnitCost],[MtlBurUnitCost],[SellingShipQty],[InvoiceLineRef],[InvoiceRef])
2

Is your filter applied to the InvcHead table criteria, or Sub-query criteria?

3

InvcHead table criteria. Eventually it will be a dynamic filter for the user in a dashboard.

Jason Woods
http://LinkedIn.com/in/jasoncwoods

4

Post the SQL from the BAQ main tab. I suspect that you need an additional criteria in there, such as InvcHead.Company = BAQConstant.CurrComp.

By having the company column in your criteria, you’ll hit one of the default indexes.

1 Like
5

If I use the InvoiceDate as a table criteria, it takes 10x longer to run the BAQ. If I put it as a criteria on the dashboard, it doesn’t return anything.

SQL from BAQ:

select 
	[InvcHead].[InvoiceDate] as [InvcHead_InvoiceDate],
	[InvcHead].[InvoiceNum] as [InvcHead_InvoiceNum],
	[OrderHed].[UserChar1] as [OrderHed_UserChar1],
	[OrderHed].[UserChar01] as [OrderHed_UserChar01],
	[SalesRep].[Name] as [SalesRep_Name],
	[SalesRep].[CSGSlsDivision_c] as [SalesRep_CSGSlsDivision_c],
	[InvcDtl].[OrderNum] as [InvcDtl_OrderNum],
	[InvcDtl].[OrderLine] as [InvcDtl_OrderLine],
	[Customer].[CustID] as [Customer_CustID],
	[Customer].[Name] as [Customer_Name],
	[OrderHed].[ShipToNum] as [OrderHed_ShipToNum],
	[ShipTo].[Name] as [ShipTo_Name],
	[ShipTo].[Address1] as [ShipTo_Address1],
	[InvcDtl].[PartNum] as [InvcDtl_PartNum],
	(IsNull(Part.PartDescription, InvcDtl.LineDesc)) as [Calculated_PartDesc],
	[InvcDtl].[SellingShipQty] as [InvcDtl_SellingShipQty],
	[InvcDtl].[InvoiceLine] as [InvcDtl_InvoiceLine],
	(InvcDtl.DocUnitPrice - case when InvcDtl.SellingShipQty  = 0 then 0 else(InvcDtl.DocDiscount / InvcDtl.SellingShipQty) end) as [Calculated_UnitPrice],
	(InvcDtl.DocExtPrice - InvcDtl.DocDiscount) as [Calculated_ExtPrice],
	(IsNull((InvcDtlCredit.MtlUnitCost + InvcDtlCredit.LbrUnitCost + InvcDtlCredit.BurUnitCost + InvcDtlCredit.SubUnitCost + InvcDtlCredit.MtlBurUnitCost) * -1
  ,
  case when OrderDtl.Company is null then 0 else InvcDtl.MtlUnitCost + InvcDtl.LbrUnitCost + InvcDtl.BurUnitCost + InvcDtl.SubUnitCost + InvcDtl.MtlBurUnitCost end)) as [Calculated_UnitCost],
	(ExtPrice - ExtCost) as [Calculated_GrossProfit],
	(case when ExtPrice = 0 then 0 else
  1 - (ExtCost / ExtPrice)
  end) as [Calculated_GrossMargin],
	(case when InvcHead.Posted = 1 then 'Yes' else 'No' end) as [Calculated_cmbPosted],
	(UnitCost * abs( InvcDtl.SellingShipQty )) as [Calculated_ExtCost],
	(IsNull(RefCategory.Description, case when Part.Company is null then 'Buy To Order' else '' end)) as [Calculated_Reference],
	[InvcHead].[CreditMemo] as [InvcHead_CreditMemo],
	[OrderHed].[Division_c] as [OrderHed_Division_c],
	[COASegValues].[SegmentAbbrev] as [COASegValues_SegmentAbbrev],
	[OrderHed].[PONum] as [OrderHed_PONum],
	[OrderHed].[NippleUpDate_c] as [OrderHed_NippleUpDate_c],
	[OrderHed].[NippleDownDate_c] as [OrderHed_NippleDownDate_c],
	[Part].[ProdCode] as [Part_ProdCode],
	[InvcHead].[ApplyDate] as [InvcHead_ApplyDate],
	[OrderHed].[TicketNumber_c] as [OrderHed_TicketNumber_c],
	[InvcHead].[DueDate] as [InvcHead_DueDate],
	[InvcHead].[OpenInvoice] as [InvcHead_OpenInvoice],
	[InvcHead].[Plant] as [InvcHead_Plant],
	(case when InvcHead.OpenInvoice = 1 then
   DATEDIFF(day,InvcHead.DueDate,GetDate())
   else 0 end) as [Calculated_DaysLate],
	(case
   when DaysLate <=0 then ''
   when DaysLate < 30 then '0-29'
   when DaysLate < 60 then '30-59'
   when DaysLate < 90 then '60-89'
   when DaysLate < 120 then '90-119'
   else '120+'
 end) as [Calculated_DaysLateGroup],
	(InvcHead.InvoiceDate) as [Calculated_InvoiceDate]
from Erp.InvcHead as InvcHead
inner join Erp.Customer as Customer on 
	InvcHead.Company = Customer.Company
	and InvcHead.CustNum = Customer.CustNum
inner join Erp.InvcDtl as InvcDtl on 
	InvcHead.Company = InvcDtl.Company
	and InvcHead.InvoiceNum = InvcDtl.InvoiceNum
	and ( InvcDtl.Company = @CompanyID  )

left outer join Erp.OrderDtl as OrderDtl on 
	InvcDtl.Company = OrderDtl.Company
	and InvcDtl.OrderNum = OrderDtl.OrderNum
	and InvcDtl.OrderLine = OrderDtl.OrderLine
	and ( OrderDtl.Company = @CompanyID  )

left outer join Erp.OrderHed as OrderHed on 
	OrderDtl.Company = OrderHed.Company
	and OrderDtl.OrderNum = OrderHed.OrderNum
	and ( OrderHed.Company = @CompanyID  )

left outer join Erp.SalesRep as SalesRep on 
	OrderHed.UserChar01 = SalesRep.SalesRepCode
	 OrderHed.Company = SalesRep.Company
left outer join Erp.COASegValues as COASegValues on 
	OrderHed.Company = COASegValues.Company
	and OrderHed.Division_c = COASegValues.SegmentCode
	and ( COASegValues.Company = @CompanyID  and COASegValues.SegmentNbr = 2  and COASegValues.COACode = 'POCOA'  )

inner join Erp.ShipTo as ShipTo on 
	InvcDtl.Company = ShipTo.Company
	and InvcDtl.ShipToCustNum = ShipTo.CustNum
	and InvcDtl.ShipToNum = ShipTo.ShipToNum
	and ( ShipTo.Company = @CompanyID  )

left outer join Erp.Part as Part on 
	InvcDtl.Company = Part.Company
	and InvcDtl.PartNum = Part.PartNum
	and ( Part.Company = @CompanyID  )

left outer join Erp.RefCategory as RefCategory on 
	Part.Company = RefCategory.Company
	and Part.RefCategory = RefCategory.RefCategory
	and ( RefCategory.Company = @CompanyID  )

left outer join Erp.InvcDtl as InvcDtlCredit on 
	InvcDtl.Company = InvcDtlCredit.Company
	and InvcDtl.InvoiceRef = InvcDtlCredit.InvoiceNum
	and InvcDtl.InvoiceLineRef = InvcDtlCredit.InvoiceLine
	and ( InvcDtlCredit.Company = @CompanyID  )

where (InvcHead.Company = @CompanyID  and InvcHead.InvoiceDate >= '1/1/2020')
6

Doe you see a difference if you use a sub-query criteria, vs a table criteria?

My thoughts are that the criteria on the table, make it first deteremine a subset of the InvcHead table, then do the joins. While criteria on a sub-query, would gather the data (like if no criteria was used), and then filter on just records that met the criteria.

7

Also… What is the point of these parameters (the highlighted parts) for the company?

image

InvcDtl.Company, must be the same as the InvcHead.Company, but then you also test InvcDt.Company against a parameter.

Are you trying to pull info from multiple companies?

8

The ultimate goal is to use this as a Dashboard filter. If I move it to a subquery criteria, it would not work the same as a dashboard filter.
I rebuilt the indexes for the database last night.
As a test, I just ran this from the dashboard.
Without the InvoiceDate filter it returns 203K rows in 37 seconds.
With the Invoice Date filter it returns 3800 rows in 56 seconds.

As a side note, I can filter by OrderHed.UserChar01 (outer-joined) and return 7000+ records in 3 seconds.

9

That was for testing. I found most queries ran faster when the top table and the outer joins all had the current company specified. It seems odd, but I assume it has to do with Company security. I have tested with them off and it appears to be slightly slower (non-scientific measuring though).

1 Like
10

What if you created a Calculated field that just holds the invoice date. A filter from a Dashboard on that would have to be applied after the full (and “unfiltered”) query completed.

Full disclosure - How BAQ’s are processed in SQL (especially how a dashboard filter is applied), is not something I know with any great certainty. Lots of guessing and speculation on my part…

11

Just curious … Is that param specified as “Skip condition if empty”, and if so, do you see any difference in speed, when that parameter is blank vs having a specified value?

12

There are no actual parameters. This is the BAQ Constant.

13

Ah…

One thing to be aware of when doing tests, is that the first time a BAQ is run it usually takes longer than subsequent runs will. I’m guessing the the SQL server has “cached” some parts of the query results and is re-using them.

I did a quick test with just InvcHead and InvcDtl (with the BAQ Constant @CompanyID)…

from Erp.InvcHead as InvcHead
inner join Erp.InvcDtl as InvcDtl on 
	InvcHead.Company = InvcDtl.Company
	and InvcHead.InvoiceNum = InvcDtl.InvoiceNum
	and ( InvcDtl.Company = @CompanyID  )

And it took 1620 ms. I removed that table criteria of @CompanyID, and then it only too 551 ms.

I added the table criteria back in, and it took only 421 ms.

So with the extra criteria, it too 1620 ms (1st run), and 421 ms (3rd run).
With out it, it took 551 ms (2nd run)

14

One thing I notice when trying to build a similar BAQ, is that the automatic links that the BAQ designer makes when you drop a new table in, prefer to link Customer to InvcDtl.

If I start with InvcHead and add Customer, it makes the expected relationships. Then when I add InvcDtl, it makes relationships between Customer and InvcDtl.

image

If I start with InvcHead and add InvcDtl, it makes the expected relationships. Then when I add Customer, it makes relationships between InvcDtl and Customer.

image

Is this because of some prefered join between InvcDtl and Customer? Or is it just because a newly added table always tries to join to last table table (i.e. 2 -> 3 regardless of the tables - assuming a relationship exists)?

15

The newly added table always tries to join to the last table number.

1 Like