How to identify active report performance issues

Check list of all posts

Active report is composed of many different decks, Sometimes; it is not easy to identify the data deck or card with performance issue, as each deck has many data containers. The best way is to use render variable.

Define a Boolean variable vDoNotRender as 1=2

We can assign this variable to any decks, any data container, and any pages.

This way gives us a flexible to control report rending without destroy report itself. We can easily pin what data container has performance issue. Technically, this idea can be applied for report authoring, please see ex -post

To identify what data container has performance issue, the whole idea is to decompose execution time for whole report, from high level to lower level. Once you have identified data container, the performance problem is then half way done.

To further identify where the problem is, we need to look into generated SQL/MDX, you can run the query to against database. 

There are two cases:

Case 1:  the SQL is slow to run against database, and then it is clear that the problem is in database side.  There are two ways to look into this problem:

Case 1.1. this SQL statement is generated by a derived query in Cognos, meaning that the query itself is from sub queries with join, union or reference.  We can look into Cognos side whether there is possibility to simplify query.

Case 1.2. This is single query from Cognos, then the whole job becomes database tuning, we need to consult DBA to resolve issue.

Case 2:  the SQL is not slow. Then again, there are two ways to look into problem:

Case 2.1 it is raw data from database, and Cognos will use local processing to handle data. The only solution is to figure out what function is not support by database, we can figure out whether there is any workaround.

Case 2.2  it is real good data, then we know the problem is not in query. It may be a chart generation issue. What we can do is to chart original chart to simple list to test again.