RS Report Running Very Slow in Cognos 10.2

[subham.ssg]

In Cognos 10.2 Report Studio, I have a report consists of 10 lists - Total 6 queries are linked with those 10 Lists. All the queries are created in such a way that they are returning summarized data and the same dataset are being displayed in the list - i.e. there is no additional roll-up aggression required by Cognos. The report takes ~3 mins response time.

When we ran all 6 queries directly in DB (Greenplum DB) in parallel sessions from DB client, the most long-running query took 37 seconds. (rest of the queries were completed in ~25s). The queries are running on a 25 Million Fact table and each query returns 5 rows only(grouped data).

There is no dependency between the Cognos queries and the following properties are set for each of the queries -
a) Auto-group summarize - YES
b) Processing - Database Only
c) Rollup processing - Database
d) Execution Method - Concurrent
e) Use for Parameter Info - NO

We're into a critical delivery schedule and stuck at this point, need expert suggestion to identify and break through the bottleneck. Please suggest.

[dougp]

First, consider the value of the data.  3 minutes may be acceptable for that amount of work.  How long would it take the user to cobble this together in Excel?

Second, look for an odd join.  Go to Tools | Show Generated SQL/MDX.  Look for a query that looks like

Code Select Expand

select T0.c1
T0.c2
.
.
.
from (
select...
.
.
.
where (TblA.Col1 = TblB.Col1 or TblA.Col1 is null and TblB.Col1 is null)
  and (TblA.Col2 = TblB.Col2 or TblA.Col2 is null and TblB.Col2 is null)
.
.
.
) T0

That indicates you have used grouping (usually with Group Span) or sectioning in a list and Cognos has made a bad choice when generating the SQL.  It's really slow and, in some cases, omits some rows of data.

I have managed to combat this issue by not using Group Span and instead using a repeater (to display the other data related to the group key) and manually configuring the Master Detail Relationship myself.

[MFGF]

Also consider this. Your report has 10 containers (lists) so 10 separate SQL queries will be sent to your database. If one query takes an average of 30 seconds for the database to process, ten would take roughly five minutes in total. The fact you are getting results back after three minutes rather than five is pretty impressive!

In this case it sounds to me like the delays are all down to how slowly the database is processing your queries. I'd be looking at ways of optimising the database to return results faster.

Regards,

MF


Sent from my iPhone using Tapatalk

[subham.ssg]

@ dougp Thanks for your suggestions. We have checked the queries, the JOINs are very simple and exactly as it should be as per data model...The Lists are also simple list (without any group span or section), it only has a decending sorting on the 1st column of the list and as I mentioned earlier, each of the list are showing 5 rows exactly as returned by the queries.

@ MFGF Thanks for your suggestion. As I mentioned, i have used the following query properties -
There is no dependency between the Cognos queries and the following properties are set for each of the queries -
a) Auto-group summarize - YES
b) Processing - Database Only
c) Rollup processing - Database
d) Execution Method - Concurrent
e) Use for Parameter Info - NO

In my understanding, the highlighted property should allow the queries to run in parallel. So, if all the queries can run in parallel then the total execution time of all queries should have been the time taken by the most long running query i.e. 37 secs.
My doubt - the queries are not running in parallel. Is there any other property available in Cognos to PUSH Cognos server to run the queries in parallel? I need all the queries run in parallel and seems that is the area of focus.

[MFGF]

@ MFGF Thanks for your suggestion. As I mentioned, i have used the following query properties -
There is no dependency between the Cognos queries and the following properties are set for each of the queries -
a) Auto-group summarize - YES
b) Processing - Database Only
c) Rollup processing - Database
d) Execution Method - Concurrent
e) Use for Parameter Info - NO

In my understanding, the highlighted property should allow the queries to run in parallel. So, if all the queries can run in parallel then the total execution time of all queries should have been the time taken by the most long running query i.e. 37 secs.
My doubt - the queries are not running in parallel. Is there any other property available in Cognos to PUSH Cognos server to run the queries in parallel? I need all the queries run in parallel and seems that is the area of focus.

Did you set the report service and batch report service parameters which allow concurrent query processing?

Even if you did, there's no guarantee your queries will run in parallel. Setting this property in a report simply makes them available for consideration for parallel processing, it doesn't guarantee it will happen. Take a look at the following post for an explanation:

http://www.cognoise.com/index.php?topic=23060.0

Cheers!

MF.

[bdbits]

I tend to agree with MFGF that speeding up the database may need to be investigated. I know it sounds like churning through 25M rows in 30 seconds is pretty good, and it totally depends on what the query is doing, but it might be this could be improved. I am not familiar with Greenplum, but if you or your DBA can run explain plans it might point to some possible areas for optimization on the database side (e.g. adding indexes and the like).

[misscognos]

I would probably do (I didn't see any mention of it so sorry if you have already looked at these things):

1. For each query, compare the Cognos and Native sql and see if there are differences. If there is anything extra in the Cognos SQL, that is an indication of local processing (exclude full outer join for a stitch query from the check- sometimes it can't be pushed down to the db and it has to be done locally). In one instance, even with processing set to db only, one of the functions was being processed locally due to incorrect usage (human error) of the function where Cognos knew how to do it but the according to the db, it was invalid.

2. If you delete everything but the container and query for each of the 6 queries, is it taking as long as you expect (i.e pretty close in line with the time you are seeing it take directly on the db). I.e. can the performance problem be narrowed down a bit to a single query/object. If there are 6 queries and 10 lists, perhaps there are lists that share a query? what about trying to narrow in on those to see if the performance is unexpected in those scenarios?

3. Just for fun, if you run the report in various formats, is there a difference. Usually interactive HTML format is faster. But, sometimes you may find that running in PDF is actually faster. There is a fun little setting called "Execution optimization" on the query that can be used to turn off first row hints. That sometimes helps as well.