BAQ Fast in Designer Slow in Dashboard

I have a BAQ that runs just fine every time in designer, but when it runs from the dashboard, it takes minutes.

What is even more bizarre to me is it happens in the morning when I come in.

I can fix it for the day by making a change to the BAQ (adding or removing a table criteria, for example) and saving it.

After I make the change, the dashboard runs great until I come in the next morning, it is slow again. This started Monday morning and so far I have been able to consistently ‘fix’ it for the day by making a change to the BAQ and saving.

I feel like I am on to something but I have no idea what. Could something be happening at night that would cause this?

Have you already tried redeploying the dashboard & clearing client cache? I’ve seen plenty of cache related issues with dashboards.

Also, testing in the BAQ designer may not be an accurate representation… since it will be limited to 1000 rows? Makes me wonder what your underlying BAQ looks like… criteria, table joins, displaying index fields, etc…?

I will try redeploying/clearing cache tomorrow if (when) it slows down again.

Could it be a cache issue if all users are experiencing the same performance changes?

The query consistently returns ~800 records so it is never limiting when I run it in designer.

I have dealt with this many times. Usually a tweak to the BAQ will do the trick. If the redeploy/cache clearing doesn’t do the trick, review these links to try a few other speed tips.

BAQ Speed Improvement TIP - ERP 10 - Epicor User Help Forum (epiusers.help)

Speed up BAQ refresh in Dashboard - ERP 10 - Epicor User Help Forum (epiusers.help)

Here is the query if anyone feels like taking a look at it.

BatchPrinting_v4.baq (81.2 KB)

I always try to include Company in my joins. This is just by habit, as things seem to work better/faster when I do. You could add Company to your subquery, and join it on Company in your top level query. You could also add company to your SysTab link. This probably is not the silver bullet you are looking for, but it might help!

2 Likes

Hmmm…
There was something wrong with table joins in the top level… not sure if it happened on the import or if something is actually wrong in your original
but… I have seen join issues similar to in others BAQs.
Seems to occur only with certain build sequences… where I might have tried something, backed up and done it differently.
Deleting and re-adding joins has always fixed these.

As NateS mentioned about Company… I always display and include it in joins unless I have a special case.

FYI… probably not an issue in this BAQ but for some tables ( e.g. PartTran)
I’ve found that including specific index fields the display can improve performance too ( ref data dictionary).

1 Like

Bruce - By ‘include’, is that just adding that field to the on the Display Fields > Display Columns tab? And the goal is to try to match one of the built-in indexes shown in the Data Dictionary for that table?

Yes

Yes… even though I always look at the indexes in DataDict
I might not always bother include index fields in my BAQs display
Usually when perf isn’t what I hoped… or when certain tables are joined…
e.g. PartTran I always try to include index fields in the display (and/or joins)
I have found it can really help with perf

2 Likes

Fun update:

Was slow again this morning. I went in and changed the order of the fields in the display fields section, saved it, and it was fast again.

Really fun to troubleshoot because I have to wait until the next day to see if it’s still slow.

Nate is right here. If you didn’t include the Company in your joins, you should. SQL is expecting this to find a good index. Also, I will often use “CurrentCompany” as a filter on the top table. Lastly, I try to use Outer Joins when possible (not sure why this is faster, but I’m not a DBA).

You can export the query plan and then import it in SQL and see what’s taking so long. Your goal is to use the indexes that are already in the database. And NateS is absolutely right because most of the indexes have the Company field in them - for example Part has the clustered index Company+PartNum, there is no index with just PartNum.
SQL’s cache is also something to look at. I had one weird case like yours with a BAQ that ran in 3 seconds while the dashboard was timing out and as soon as I cleared SQL’s cache the dashboard went back to normal. It didn’t last long though. In a couple of days after clearing it, it went back to timing out.
I didn’t have enough time to get to the bottom of this but what currently works for me is a daily/weekly maintenance plan in SQL that reindexes the tables I have in that BAQ. My plan is to get rid of the dashboard altogether so I stopped the work on this.
If you have access to SQL and want to play around in your pilot have a look at the SQL commands DBCC FLUSHPROCINDB for the entire DB’s cache or DBCC FREEPROCCACHE for a specific query plan.

1 Like

Hi all, I am having a similar issue to this post. I have a BAQ that ran in 3 seconds in BAQ designer, but takes about 2 minutes in a dashboard. I ran through some of the tips in here and cut it down to 1.5 seconds in the designer, but no change in the dashboard. It does have the strange behavior listed where a small change will make it run quickly in the dashboard until the next day when it is slow again. Any new ideas would be appreciated.

This sounds like parameter sniffing. Depending on what your BAQ looks like, this may or may not work, but one of the ways to try and make the SQL plan work for better is to optimize for unknown parameter. When SQL makes an execution plan, it does things in order to reduce the amount of records returned as soon in the process as possible. That will depend on what you are filtering by. It will make the plan and run. Then when you change the filters, it doesn’t make a new plan, it uses the old one. But if the parameters are different, it’s not a crappy plan to use. This option will force it to make the plan as if it doesn’t know the parameters, and can be a better overall plan for when you have different parameters.

image

1 Like

Brandon, thanks for the quick response. This seems to work, however the OrderBy setting overrides the sort order in the query. Is there a way to have both?

You can move the orderby that you have in the query to here, then just tack on the option at the end of that. There isn’t a built in way to drop that option yet. (it’s either in the newest version, or about to be). So you just need to be able to add it to the end of the query, and this is a way to hack it. The orderby 1 is just something that doesn’t normally do anything. But you can make it a valid order by.

Now that you say that I do remember hearing that somewhere before. Thanks for the help!