If the BAQ has parameters odds are itâs a case of Parameter Sniffing, and @Banderson 's suggestion should work like a charm.
Thank you @Banderson for the suggestion.
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.
However, thank you again.
DaveO
what is in query execution plan?
Itâs probably âworkingâ it just may not change anything. Sometimes it helps, sometimes is doesnât.
Has it been run since the last upgrade?
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.
For Giggles can you try
OPTIMIZE FOR UNKNOWN
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.
You guys are AWESOME.
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.
Query option was added in 2023.1
However there are ways around it using group by hacks
or ORDER BY
Ms. Olga: WOW that is some Jedi - Voodoo
Thank You!
Order By 1 OPTION (RECOMPILE) seems to have fixed it, but unsure why. Iâm not sure why I would see inconsistencies so drastic between users, even on the same computer?
Does anyone know if Epicor, or in here, if there is a good list of execution properties, what they are used for and how to use them? I did not see anything from Epicor just individual threads in this forum.
Google for parameter sniffing in SQL server.
Option RECOMPILE is quick and dirty fix and creates different execution plan on each run that also consume resources. You should not use it for queries that run often - like once a minute or less