Speed up BAQ refresh in Dashboard

Hello all you big brains out there!

Today I have a very broad question. Without delving into specific use cases, are there any obvious ways to speed up the refreshing of BAQs in a Dashboard?

I did find a useful tip that I will be utilizing in my BPMs:

Has anyone else adopted any little tricks like this for speeding up your BAQs?

Thanks for your time!
Nate

Usually just good SQL practices:

  • Limit records before linking (use the SubQuery)
  • Make sure to utilized indexes during linking and selection
  • reduce the number of fields to the minimum
1 Like

Thanks Mark. Good advice. Can you expand a bit more on utilizing indexes for linking? I don’t see indexes referenced in my BAQ designer.

Check out the Data Dictionary Viewer. It lists the indexes that Epicor added (but I doubt user-added). In your links, try to include as many as you can from left to right within the index. There are some gotchas that knowing the right index will help.

2 Likes

From time to time I review the execution plan. In the BAQ designer, you can get it from Actions -> Get query execution plan. You can then open it with SSMS. It really only helped me out for one particular situation, but it is something that is available to you.

image

2 Likes

FYI: It actually does show the user ones. This is one that I added.

That also raises another tool which is to add indexes to your DB to speed up a call, but it should be done sparingly. Use the Query Execution Plan to get an idea on what you would add for a problematic query if the other best practices aren’t good enough.

3 Likes

Good to know. Doesn’t really apply to us Cloud Users until adding indexes is added to the model change procedure.

:+1:

I have a BAQ that bounces our existing order/releases from the order tables, against my custom UD02 table. Could I speed up the BAQ by using some of the key fields for values that I need to search on? Right now I am just using one key field for a unique ID. Is this a standard practice?

It all depends on the project. If I have a UD table that is related to a job material I am going to use the keys for things that aren’t going to change, but are related to what I am looking for. So for that example:

Key1: JobNum
Key2: AsmSeq
Key3: MtlSeq

I know with the combination of those 3 things that it will be unique, so it fits using a key, and there already indexes on those items, so the lookup with be fast. But I wouldn’t use this in a situation where I would want to change what things they are related to, or I would have to understand that I have to delete and recreate the row if I need to to that.

So it depends on how you are looking up your data and how it needs to be accessed and changed. There really isn’t a one size fits all, all projects are going to have different requirements that will take different approaches as the best way.

1 Like

I just got word from support: Since I am a cloud user I don’t have access to the Execution plan. :roll_eyes:

More importantly, we don’t have SSMS! The the Execution Plan would have to be read manually.

You don’t have to have SSMS hooked up to anything to read the execution plan. And you can download it for free. (It doesn’t change the fact that you can’t get the execution plan though)

True. Not sure what the reasoning behind it though. Non-SaaS cloud users do have access though, which is good since you’re the one paying for processing. Since Epicor pays the Azure bill in SaaS, you might think they’d want to give the users tools to reduce the costs. :man_shrugging:

1 Like

I found that adding ‘Company’ to the top of all my table relations drastically sped up at least one of my BAQs. Went from taking minutes to only a few seconds! I also found a few more helpful speed/efficiency tips here:

Every Vote Counts

1 Like

Yeah, that was covered in this post, under utilize the indexes. Company is an index on almost every table in the system.

1 Like

Alright, I’ll ask the dumb question. What is “utilize the indexes”?

I am accustomed to thinking of primary keys, and I observe that the first index in the pic is called PK, so I’m thinking I’m in the ballpark…

So, does “utilize” simply mean that in the Table Relations part of BAQ Designer, you want to join on as many primary key fields as possible?

For example, (and I know this one is automatic anyway), to join OrderDtl to OrderRel, you want to join on Company, OrderNum, and OrderLine - because those are primary keys that are common to both.

I mean, OK if it speeds things up, but I see this as utterly necessary, just to avoid duplicate results.

I feel like I am missing something from the experts here…

There are more indexes then just the with the primary keys. An index basically just chops up the lists list so that the system can scan fewer records to find what it needs. If you look in the data dictionary, it lists all of the indexes that are built into the DB. Usually it ends up being pretty related to how you are joining the tables and that’s why a large majority of the time the dictionary automatically adds those relations, and it’s usually the right way to go. But if you have a temp table or a subquery where the dictionary isn’t automatically in effect, if you can use the combination presented in one of those indexes, it will help speed things up. Sometimes that mean adding a field to the subquery that is irrelevant to your final goal (like company in a single company system) but it still can help the DB find items faster.

3 Likes

Gotcha.