Has anyone ever used this and examined the results in SQL Server SSMS? We have a carried over BAQ from E10 that ran great there but is pretty slow in E10.1. I’ve accidentally clicked on this before when intending to open Execution Settings but never saved the file.
Anyway, I did today because the dashboard this BAQ runs is used by nearly everyone. It recommended adding in a new index. I’m leery to be doing that though!
Indexes are weird. They take up space, need to be updated, can be duplicative and can speed things drastically based on your usage.
Always interesting to examine recommendations for knowledge gain. I’d never criticize that. I’ve also seen updates slow down during the index update (Of course I usually start getting involved when something happens like the Part table having 14 million records so…)
Knowledge accumulation is always the best place to start.
I tried it on our test server and the Dashboard pulled data 75% faster! Tomorrow is a new refresh of the test server, will try it with “fresher” data set but it’s not hard to remove a index either.
Check update perf and db size and … you know the sql 201
Great to learn about a new functionality.
Just have to be a bit careful with acting on what it suggests since the suggestion may become irrelevent, depending on how the BAQ is used. Consider the conversation in BAQDataView and a baq parameter thread, demonstrating that the SQL being executed is generated dynamically. For example, you might have the query doing a sort on column PartNum in the BAQ, but be filtering out the results to only 3 parts when used filtered in a dashboard, there could be the recommendation to add an index on PartNum for the BAQ, but that will only add overhead for writes and rebuilding of indexes for microseconds (if that) for your final query.
@AndrewM agreed, there is all kinds of features in Epicor that don’t get much discussion.
The whole reason I’m looking at the BAQ is user dashboard performance reasons. IIRC, it has the most filters in the Tracker than any other dashboard we have. For context, it’s an “Open Orders” BAQ listing open releases, what remains to be shipped, calculated fields include DaysLate, Part OnHandQty, Avail Qty, Allocated Qty, FulfilledQty, Est Delivery-Ground, Est Delivery-Express, and some others. So you can see it pulls from other big tables; PartAlloc & Part for calculation data.
I’ve also toyed with rebuilding it, it’s a old E9x BAQ with the E9 style IsSummary flag, but I’m not sure it’d really increase performance.