Slow embedded dashboard?

Kinetic 202X
1

I’ve added an embedded dashboard to Quote Entry. When I select a Quote # (and the dashboard filters based on customer number, it is now taking ~10 seconds to load the quote instead of normal 1 second without the embedded dashboard.

In BAQ editor, the query pulls 2200 rows in 241ms.

In Quote Entry, when I select a quote, server trace log shows BAQ execution time jumps to eg 9137ms. This is filtering down the 2200 results to 77.

When Quote Entry first loads, the embedded dashboard loads all the rows instantly, no problem. So it is something to do with the filter. But I haven’t had this problem filtering on other programs with much more complicated BAQs.

Any guidance here? Thanks!

2

I was able to trace it all the way back to the query, not an Epicor issue. The filter essentially adds a WHERE clause to the BAQ query, and this extra criteria makes the query run very slow in BAQ designer and SSMS. I’m stumped as to why it is so slow here. I have other dashboards with subqueries and no problem filtering.

Can someone explain this? Or point me in the right direction to fix? Thanks

declare @companyID as nvarchar(2)
set @companyID = 'LI'

--Below from BAQ designer
select 
	[QuoteHed].[CustNum] as [QuoteHed_CustNum],
	[QuoteCnt].[ConNum] as [QuoteCnt_ConNum],
	[QuoteCnt].[Name] as [QuoteCnt_Name],
	(count(QuoteHed.QuoteNum)) as [Calculated_NumQuotes],
	(count(MinQuoteNumPerSalesOrder.OrderDtl1_OrderNum)) as [Calculated_NumOrders]
from Erp.QuoteHed as QuoteHed
left outer join Erp.QuoteCnt as QuoteCnt on 
	QuoteHed.Company = QuoteCnt.Company
	and QuoteHed.QuoteNum = QuoteCnt.QuoteNum
left outer join  (select 
	[OrderDtl1].[OrderNum] as [OrderDtl1_OrderNum],
	(min(OrderDtl1.QuoteNum)) as [Calculated_MinQuoteNum]
from Erp.OrderDtl as OrderDtl1
where (OrderDtl1.Company = @CompanyID  and OrderDtl1.QuoteNum > 0)
group by [OrderDtl1].[OrderNum]) as MinQuoteNumPerSalesOrder on 
	QuoteHed.QuoteNum = MinQuoteNumPerSalesOrder.Calculated_MinQuoteNum
where (QuoteHed.Company = @CompanyID)
and (QuoteHed.CustNum = 1171)  /*adding this criteria increases execution time by ~40X */

group by [QuoteHed].[CustNum],
	[QuoteCnt].[ConNum],
	[QuoteCnt].[Name]
3

Found a solution. CustNum is not an indexed column on QuoteHed; apparently using subqueries and then adding where criteria on non-indexed columns can be very slow.
Luckily QuoteHed.BTCustNum is an indexed column, and for us, the two are always the same. So when I switched the embedded dashboard filter to run on BTCustNum, query now takes 77ms instead of 9000.

2 Likes