asked on November 24, 2017
we are experiencing some performance issues with few of our reports.
We have a report that uses MDX that executes quite fast on the server but when we call it true flexmonster the report is freezing on "waiting for data" and on the server we have 100% CPU and few GB of memory eaten up for 1 or more minutes, equally by the flexmonster accelerator service and the MS Analysis service.
We found that the accelerator service is calling quite different MDX than the original we pass to the flexmonster report. In the attached file contains the original MDX, the one that accelerator service is using and the error we are getting.
Please advise, is it our config of the flexmonster report that causes the accelerator service to transform the MDX in this way and can we do something about this? As is the actual MDX that the flexmoter is using to get the data is next to impossible to be executed on our server but the original we put in the JSON config is something that is executing for 2-3 seconds tops.
best regards,
Nikolay
⋅ November 27, 2017
Hi Nikolay!
It's hard to reproduce this problem without access to your data source. Is it possible to the dump of your cube or any sample with such kind of error?
Thanks,
Roman
⋅ December 21, 2017
Hi Nikolay!
I would like to inform you that we already started an investigation and need some help from your side.
Could you please send us the sample of your report? You mentioned that resulted MDX is different from the original and I want to see where and how this original MDX is defined.
Regards,
Roman
⋅ December 22, 2017
Hi Roman,
please find the attached file.
best regards,
Nikolay
⋅ December 22, 2017
Hi Nikolay!
Thank you for the answer. Unfortunately, it's still unclear how you get MDXes that I can see in your samples. Do you use subqueries or filters? Could you please clarify?
Thanks
⋅ December 22, 2017
Hi Roman
Just attached the report config we use, the MDX we use and the log from flexmonster axelerator that end up executing when we open the report. What is in the axelerator log is clearly not the MDX we are using original, they are multiple additions mainly on the first line of MDX.
the thing is, when we call our original MDX directly true the MSSQL management studio we get quick response, if we call the MDX from the Flexmonster Accelerator (attached in the zip) the SQL server jumps from 3GB memory to 9GB and it is not responsive for 2+ minutes
we are using ver 2.410 and all cache is cleared before we run the report
⋅ December 22, 2017
I've tried to run your config with your cube and didn't see MDX like yours. What I'm doing wrong? Why subselects appear in your query?
Regarding comparing the running time of two queries. They had different subqueries and it's a source of the problem. So I need to understand why and how these subqueries appear and still need your help here.
⋅ December 22, 2017
Hi Roman
we dig some more in the reports and we found jscript error when running the report (details attached in the file)
VM170:1 Uncaught SyntaxError: Unexpected token I in JSON at position 6285
at JSON.parse (<anonymous>)
at D.Flexmonster.D.Uy
at D.Flexmonster.D.Hn
at XMLHttpRequest.h.(anonymous function).(anonymous function)
also, we have noticed that during the transfer of the report config from our configs in the DB to the flexmonster report all the newlines and line breaks get replaced with /n/ t, is this something that may cause the problem ?
⋅ December 25, 2017
Hi Nikolay!
Thank you very much for your report with a subquery. It helped really much.
We will fix the exception with JSON, but it's not the source of the problem.
After testing with your report I can say that there is no problem with the accelerator, and it works correctly. I was able to run the query generated by your report and returns 24 828 513 rows as a result. There is no surprise that it takes a big amount of memory and requires several minutes to run.
I can recommend you two solutions in this case:
Have happy holidays!
⋅ January 17, 2018
Hi Nikolay!
Did you have a chance to check the accelerator with different subqueries? Does it work better?
Waiting for your response,
Roman
⋅ January 18, 2018
Hi Roman,
We just installed ver 2.412 and start running all reports on it. Your suggestions are improving performance but as far as reducing number of rows, it is not an option for our live systems (actually for Live we need to significantly increase the data)
Anyway, we found that most of the MDX actually runs quite fast (even after the accelerator adds few lines), the attached file returns few thousand lines in 5 sec on the server (after we increased the page size to 100 000), but the actual report is going for 5-10 minutes grabbing the data form the server in 40 rows chunks.
you did send us few weeks(may be months) ago special version when we first noticed the problem and that ver was grabbing data in 1500 row chunks .
can you do a parameter or something so we can control how big the server requests must be so we don't loose time in browser-server communication?
best regards,
Nikolay
⋅ January 19, 2018
Hello Nikolay,
Thank you for writing.
Your suggestion sounds reasonable. We will add the necessary improvement in the version 2.414 ETA Feb12.
Regards,
Dmytro.
⋅ February 13, 2018
Hello Nikolay,
We are glad to inform you that the version 2.414 is already available on our website. We have added two new properties to flexmonster config:
MEMBERS_CHUNK_SIZEDATA_CHUNK_SIZE
Waiting for your feedback.
Kind regards,
Dmytro
