Hi Frnds,
I have a report which consist of 12 charts, one query per chart, that makes 12 queries per report. The report takes 2 mins 30 seconds to run. In order to reduce the time taken, I thought of running these 12 queries in parallel.
I have done the following changes for 12 queries -
local cache : yes
execution method: concurrent
Processing : database only
Rollup Processing : database
Execution Optimisation: All rows
Use for Parameter Info: Yes
Then, I have set the following parpamters for both batch report service and report service -
RSVP.CONCURRENTQUERY.NUMHELPERSPERPROCESS 10
RSVP.CONCURRENTQUERY.MAXNUMHELPERSPERREPORT 10
I believe there is something else which is missing, as i could see the report takes same time (2 min 30 seconds) when run in sequential or concurrent.
Have been tweaking all these settings for a week now, Please help !!!
Shrikant
Have you visited with your friendly DBA yet? Database server configuration can affect how much parallel processing you are allowed to do. He/she should also be able to run a trace when your report is executing and see what queries are coming through and when they arrive. Last, again depending on database configuration, if your queries are all accessing the same tables there is potential for database locks interfering, but this is probably not the case.
I would also note that those service settings (RSVP.*) are global in nature and are going to affect more than just your one report. They may be appropriate for your environment or not, I have no idea, but generally tweaking your server for one report is not usually a good approach.
Concurrent processing will happen only if cognos server thinks it'll speed up things. You set everything required, as far as I can see.
Try running trace as in the document below and you'll see if queries are indeed ran parallel:
http://www-01.ibm.com/support/docview.wss?uid=swg21440230&aid=1
In my experience, long running reports are most
likely stuck at cognos side with local processing & complex rendering. But I'm usually responsible / have access to DB layer as well, so things are indexed and stored properly.
Sent from my iPhone using Tapatalk - now Free (http://tapatalk.com/m?id=1)
Hi ykud,
Thanks for your reply.
we tried the steps mentioned in the doc, but no luck. command not giving any output.
bash-3.00# BIBusTKServerMain port=1234
port=1234 pid=29273
>
>
>
The only differnce we can think of i.e cognos installed on solaris so we have tried the same stpes on it.
i belive this shouldn't be the problem.
Please suggest.
shrikant
Hi Shrikanth,
Are you using tomcat as application server?
Sent from my iPad using Tapatalk - now Free (http://tapatalk.com/m?id=1)
HI ykud,
No, it has been deployed on IBM Websphere server.
Shrikant
Hi Shrikant,
Were you able to get the queries run in parallel? And I have another question, does concurrent execution apply to MDX queries?
I have a dashboard built in report studio that has 8 objects in it (5 charts and 3 crosstabs). Each object has its own query and each query has appx 4 slicers.
If these charts and crosstabs are run individually, the longest running chart takes appx 5 seconds to load so I was expecting the dashboard to come up in 5 seconds, but it takes appx 25 seconds to load and that is the sum of each individual chart.
I have set the execution mode of each query to "Concurrent" within report studio and have the dispatcher advanced settings set to allow parallel execution
(RSVP.CONCURRENTQUERY.NUMHELPERSPERPROCESS
RSVP.CONCURRENTQUERY.MAXNUMHELPERSPERREPORT
RSVP.CONCURRENTQUERY.ENABLEDFORINTERACTIVEOUTPUT
RSVP.PROMPT.EFFECTIVEPROMPTINFO.IGNORE)
The report runs on a dynamic cube. I have tried using powercube and DMR as well as data source but that also ended up running in sequence.
I have parallelism enabled within SQL Server but that did not help.
If anyone has any insight on this or was successful in turning on concurrent execution please let me know.
Thanks,
Kanwar
Alright so this is what IBM had to say:
"When concurrent query execution is enabled, the report server determines which queries in the report can be run concurrently.
However, the report author must also specify the queries in a report that are candidates for concurrent execution. In Report Studio, you have to set the Execution Method of a candidate query to be "Concurrent". I assume this was completed.
.
However, with all of this done, keep in mind that there's no guarantee that the query(ies) will be executed concurrently. It's all up to the report server to make the final decision. All you're doing is telling the report server which ones you want it to consider for concurrent execution. There is no documentation on what governs when a query can be run concurrently as this is an internal process, however having is set allows the report engine to analyze and determine if the queries can be run concurrently based off a number of factors. Also it is important to note this feature does not guarantee improvements in performance. In fact the results could be better, neutral or worse than non-concurrent execution. Basically, if this was guarenteed to help,
it would be enabled by default, so, ultimately you will have to test this out and determine if it work for your situation.
The following might also be useful:
How to trace Concurrent Query Execution?
www-01.ibm.com/support/docview.wss?uid=swg21440230
Here are some known report design items that will prevent concurrent execution:
.
1) The query is used in a conditional block whose condition is not static
2) Queries used for cascade prompt controls may prevent concurrent execution
3) It can't be a detail query
.
In summary, there are no documented limits BUT it is dependent on many variables (i.e.) number of processes, report servers, CPU, memory, database architecture and ability to support parallel query executions (Oracle, MSSQL) etc "
I just settled down with creating around 30 cache priming reports scheduled to run every 12 hours to achieve the desired performance and is working good so far with reports taking less than 5 seconds to load that took 30-60 seconds once. Drill down performance is good as well in the nested crosstabs and the results show up in less than 10 seconds (3-5 hierarches in the rows and 6-8 measures in the columns).
Quote from: kanwar89 on 29 Jul 2015 02:32:38 PM
Hi Shrikant,
Were you able to get the queries run in parallel? And I have another question, does concurrent execution apply to MDX queries?
I have a dashboard built in report studio that has 8 objects in it (5 charts and 3 crosstabs). Each object has its own query and each query has appx 4 slicers.
If these charts and crosstabs are run individually, the longest running chart takes appx 5 seconds to load so I was expecting the dashboard to come up in 5 seconds, but it takes appx 25 seconds to load and that is the sum of each individual chart.
I have set the execution mode of each query to "Concurrent" within report studio and have the dispatcher advanced settings set to allow parallel execution
(RSVP.CONCURRENTQUERY.NUMHELPERSPERPROCESS
RSVP.CONCURRENTQUERY.MAXNUMHELPERSPERREPORT
RSVP.CONCURRENTQUERY.ENABLEDFORINTERACTIVEOUTPUT
RSVP.PROMPT.EFFECTIVEPROMPTINFO.IGNORE)
The report runs on a dynamic cube. I have tried using powercube and DMR as well as data source but that also ended up running in sequence.
I have parallelism enabled within SQL Server but that did not help.
If anyone has any insight on this or was successful in turning on concurrent execution please let me know.
Thanks,
Kanwar