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:
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.
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.
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.
Good to know. Doesnât really apply to us Cloud Users until adding indexes is added to the model change procedure.
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.
I just got word from support: Since I am a cloud user I donât have access to the Execution plan. 
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. 
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:
https://luxent.com/reader-request-improving-baq-performance/
Every Vote Counts
Yeah, that was covered in this post, under utilize the indexes. Company is an index on almost every table in the system.
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.
Gotcha.
