We did careful testing of our critical dashboard in the Pilot environment prior to the leap to Kinetic this past weekend. This dashboard has about 10 subqueries and has been running on the cloud for about six years. What could go wrong?
The dashboard is important because we run it to determine critical shipment information that is passed back to a supplier of ours. The information is required to be submitted by the 9th of the month for the prior month’s shipments.
We tried on June 4th in our Production Cloud environment and the dashboard/BAQ would not run. Thinking it was an issue with pre-cutover activities, we checked in Pilot and it ran just fine.
Monday morning went in to run the BAQ and no results except for an error code.
Copied the BAQ from Pilot into Production - still no luck.
Ran into @timshuwy at the NW EUG meeting - he gave me some really good advice.
Applied his steps…
Spend some time tuning the BAQ thinking maybe it was a speed issue. - no luck.
Sent in a ticket to support - EpicCare was right on it, logged into our systems and got the same results. BAQ runs in Pilot not in Production.
Hmmm…
Recommended this knowledge link.
Knowledge Article KB0049808 - BAQ slow performance
Following the steps I put in “QueryOldCompanySecurity” in the execution parameters. Another workaround if the issue started happening since a new upgrade is to revert back to the previous BAQ execution method for older versions. To do this, click on Actions > Execution Settings and then add a new line in this window and select any option here, then select it and manually edit it to say “QueryOldCompanySecurity” and set the value to “true” and enable the Persist on Query checkbox, then click OK.
This worked and we were able to get the report out (one day late)
Still curious why it would run in Pilot and not Production. I thought that these are identical environments if that is what we just spent the last six weeks testing Kinetics in?
Glad that you were able to get it working. Sorry my solution didn’t result in the final solution, but that is the way it goes sometimes. As @josecgomez suggested, the Caching thing can sometimes get in the way… Just yesterday, it was announced that Epicor had a new Kinetic page referenced on our website, and so I followed the link. In Google Chrome, it failed… but MS Edge, it worked. Other people didn’t have the problem, so even though it looked to me like the website was down, it turned out that I had to delete my cache in Chrome, and everything worked fine.
Don’t think that this was a memory cache problem because multiple people including tech support were not able to run the BAQ in Production but they were able to run the same one in Pilot.
Your suggestions of changing the joins did speed up the BAQ. Haven’t done the time trials yet, but I think it was cutting it in half.
if you want to see your query go even faster, write it as an External query. when you do this, then no additional filters / security are applied. I had to do this to one exceptionally troublesome query I wrote. When I was done, it was good.
ALSO, I found recently that if i had a sub-query that was gathering some specific data, I could turn that subquery into a CTE, which optimized how SQL handled the data. It turns out that (as I understand it), the CTE is run FIRST, and it creates an in-memory table, which is then joined. because it is run one time, it doesnt have to re-retrieve the data for each record in the top query.