Now before I jump to create views for all slow BAQ what steps can I try to improve performance. We have a few complex BAQs that are slow. I am guessing they are similar behind the scenes.
The reason for this is that you are bypassing all the Epicor Logic (that may be ok, but keep that in mind)
Customer Security
Territory Security
Payroll
Field Security
etc.
Since we cannot see the actual BAQ, we can only guess… but I do know that there are several versions of Epicor where joins containing the COMPANY fields slow down queries. Example, if you query OrderHed, and join Customer, it will be slow until you delete the Company field’s join, and instead add a Table Condition that the company = current company.
So… one thing to try is simply removing the Company from the join. Same thing for “PlantID” field in some queries.
The joins should be done by an index. Removing or Adding the company is incorrect, the proper answer is to join on the indexes. (if you can)
If you look in the data dictionary it lists all indexes, joining on ANY of these indexes should igve you better performance, not joining on at least an index will require a full table scan.
I’m just curious, is it possible that the execution plan stored for the original BAQ was a “bad” or sub-optimal plan and that running the View (which I’ve read does not cache execution plans) runs faster without being forced to follow a possibly bad cached plan?
And from what I’ve read it sounds like Indexed Views can run even faster.
Are we able to create additional Indexes in Epicor to help queries run faster? Maybe that’s what you need for this particular BAQ? (Been watching too many Brent Ozar videos… )
Yes, you are getting that right… we have reported it, and can reproduce it. Best practice says to always include company, but for CUSTOMER we found we could not. Some BAQs went from minutes to seconds.
That funny blue UD widget may be the problem…
I actually ran into that today, only worse. I added a table, added UD Fields to the BAQ, saved, tested, and all was well…
BUT upon returning, i had the UD table showing (it should never be there)… and worse, it was not joined to anything. So… it took even longer.
In E10, there is an ERP view that makes it so that you should not have to refer directly to the Erp.PartXRefVend_UD directly. It should already come along for the ride with its parent.
Thank goodness I saw this post!! I was joining on company with the customer table and it was fast, until some other part of the query changed. Then it was an issue for some reason.
I just ran afoul of the Customer voodoo. We had a BAQ that was running fine for over a year. Then last Wednesday it suddenly started timing out. It was timing out in the REST API as well as in the BAQ Designer. @ERPSysAdmin’s guess that it might have something to do with a cache prompted me to create a new BAQ from scratch, adding features one at a time and testing at every step.
I joined Customer to OrderHed and all was well. The query returned ~500 rows in ~50ms. Then I added a subquery criterion on a custom field on OrderHed. The BAQ became so slow that it couldn’t even return 10 rows before timing out. Removing Company from the Customer join fixed it. Now it runs in ~150ms. @timshuwy, I owe you a beer!
Jose, I have a query over UD19 that takes quite a while to run as well. We were reading this post and so we converted this into an external BAQ as a test. And this did substantially improve the speed on this query. Before putting this into our Production environment, however, we want to make sure we understand the pros and cons. A little background is we have one company in Epicor and we don’t use any customer security so everyone can see all customers and we don’t use the payroll function in epicor. Is this a bad idea to change this to an external BAQ and to change some of our other long running queries to external BAQ’s? What is the downside?
So much for a best practice!
)
