We have a large BAQ (not written by me) that has been run every month for the last 2 years. The BAQ typically runs in about 40 seconds.
We recently tried to run the BAQ and discovered that it now takes 2 hours and 3min
I created a support ticket and the support person seems intent on reverse engineering the BAQ. I suggested that the BAQ should be fine. It has not changed in 2 (maybe 3) Years.
Our setup is currently running Multi-Tennant on Server 201.
Has anyone else come across this kind of performance issue recenty?
I tried that execution setting - I do not think it is working. I ran a test for a range of One day and it took 12min - that is the same number was getting earlier.
Did you upgrade recently? We saw the same issue after the flex 2 cloud upgrade on 8/26. I had several BAQs and related dashboards doing the same thing. A query that would normally take 5 seconds would take 90. I had to increase the queryTimeOuts to get us going. They would run normally at certain periods of the day for about a 1/2 hour and then back to painfully slow. For several days at about 1:30 est to 2:00 would run normal then slow the rest of the time. A couple trace log lines from that time testing on the same queryâŚ
1:30pm est
2:15 est
Support was not much help but I kept bugging them and escalating. They finally did an appserver recycle and things are back to normal. I suspect they did something else but they are not giving me any other info.
RECOMPILE just throws away existing execution plan, which probably already was discarded after a month of not being used. It is just new plan is not as good as old. Maybe data changed, maybe statistics, or both.
Mr. Brandon: It has not been run since the last upgrade.
Also, the Classic UI fails with a server timeout error?
If i run the BAQ logged as âBrowser ONlYâ and run the BAQ as a Kinetic UI - then i do get records back - it just takes forever (or seems like it - i am not a patient person). I am currently at 15min and still waiting for the records to show up. Again this was a BAQ that until the last upgrade ran in about 40 seconds.
Instead of RECOMPILE @DaveOlender. If its a parameter sniffing issue this might solve it. If this and RECOMPILE didnât help then its bad Stats and or Idex issues and for a shared cloud instance thatâs gonne require Epicor to do something.
Thank you @josecgomez and @Banderson - the OPTIMIZE FOR UNKNOWN and âUseAlternateCompanySecurityâ combination did the trick. The full month now runs in 49Seconds.
Well, now Iâm curious, whatâs âAlternateCompanySecurityâ? Iâve never seen that before, and if the name is anything like it sounds⌠we could use that! lol.
Mr. Brandon: I am not really sure what it does. I run into this whenever a baq runs in Pilot but not in Live. Epicor Support suggested this as a fix many moons ago and i found that i can switch between it being on or off and it will help us get the BAQ to run.
I suspect it has something to do with the Pilot and Live servers being patched differently (total guess there).
It uses old company security that was reimplemented at some point.
In some queries it works better, because sql server creates better execution plan.
So it is up to you to test both and to select what works for you. Or use extbaq like you do already
Do you know if this is only available in newer versions. We are on 2021.2.8.I can add UseAlternateCompanySecurity, but not the QueryOption even if I type it in.
I have a BAQ that is executing slow (152 rows returned in 200,000-450,000 ms) for some people, but fine for others (152 rows in 30ms). It seems like this could solve the issue.
