Categories
Export result of one Analysis, how avoid the rerun of the query
I have a very complex analysis, which requires a lot of execution time, something like 35-40 minutes.
After the execution, I need the export of the result, in PDF, the problem is that it start another time the execution of the query….
It is possible avoiding th rerun of the qery, before the export ?
In the Advance section I have set in the space for Prefix this conditions:
SET VARIABLE DISABLE_CACHE_HIT=0, DISABLE_CACHE_SEED=0;
but nothing changed.
It is very mandatory solve this issue, for the usability of the analysis, avoiding the restart of the query execution, any idea ?
Many thanks.
Fabio
Answers
-
Hi @user8945294 ,
I would use an Agent and define a proper schedule to do the job. In this way, the analysis will be executed only once, and the results delivered in the selected format to one or more recipients.
1 -
@user8945294 - From the tags selected, it appears you are using OAC. In OAC, this behavior is by design and cannot be changed. However, please note that OAC has a query timeout limit of 660 seconds, so there is a possibility that the query will hit this limit. It is recommended to redesign the analysis to ensure the execution time is shorter.
You can also use agents to seed the cache. Please refer the below document for details on the same.
But, you need to remove the below request variables from analysis as it bypasses the cache in every run.
SET VARIABLE DISABLE_CACHE_HIT=0, DISABLE_CACHE_SEED=0;
1 -
Hi @federico
sorry but I cannot do that, because this analysis is a part of a dashboard page, with prompt filter given to the users as a part of a complete dashboard. So I cannot run it as an Agent.It is possible in other way?
Thanks!
Fabio
0 -
Hi @user8945294 ,
Is the server cache enabled? If you are on OAC/OAS, you can check it from the Console page > System Settings. The variables in the Advanced tab of the analysis editor are typically used to disable the cache for a given analysis, and are not required.
If the cache is enable, then you have to check that your analysis has actually been cached. You can do this by having a look at the query log or usage tracking. There are some functions (e.g. CURRENT_DATE) that prevent your result set to be cached, so it's worth checking this as well.
Last, but not least, you should consider to redesign your analysis (or data model) to achieve better performance.
1 -
Thanks for the answer @Federico.
Actually I have used CURRENT_DATE in the code of the analysis, so the analysis logically will not cached.
I tried to set the cache just to find a way not to rerun the query, it was an idea to prevent the double execution.
My disappointment is related to the fact that the code is running again when I click on export, in my opinion is not completely logic, since that it was executed a sec before, why do it needs twice execution just to have an export?
So the core of my request was, there is a way to avoid it. But reading the answer of @Sumanth , it seems to be a standard for OAC analysis.
So no solution to avoid rerun.
Thanks to all!0
