Pageset performance / alternative

[blom0344]

We are racing against the clock to regain performance with a small report that is based on a pageset. At least 6 crosstabs/list are coupled to the main query. With pageset a report of 4 pages takes  > 8 minutes.  The same data without pageset takes only 30 seconds (generating a silly 1 page, but confirming individual queries run pretty fast)

Anyone a suggestion on how to improve performance?  Is there a good alternative for a page set I may be missing?

[navissar]

Page sets are a real problem, because page sets are Master-Detail Relationship. These are an eye-candy, but can degrade performance immensely.
What happens in a master-detail relationship is that Cognos sections and filters the detail query locally, which is, well, infamously slow.
Alternatives? *sigh*. They're not very good, but here they are:
1. If you are breaking the report based on a static field (That is, a field which doesn't change much). break it manually. Yes, it's horrible, but the report will fly.
2. If you're on relational, you could try and build a cube. Dimensional reports can use the built in page layers, which also flies pretty fast.
3. The guidelines here http://public.dhe.ibm.com/software/dw/dm/cognos/reporting/performance_and_tuning/Improving_IBM_Cognos_Report_Studio_Master_Detail_Report_Performance.pdf may some times help.

[blom0344]

Thanks. This is an 'inherited' report, so we have to cope with what we got. Dimensional is no option due to the complexity. The problem is that it was originally build using one single value for the master query, which gave reasonable performance. We have no issue with pagesets when simply 1 or 2 report  lists/crosstabs are linked to the master query. But with 6 the report dies..

We noticed the massive local processing, but thought it was primarily caused by the complex unions etc within the report. After clocking individual queries (which were all pretty fast) we noticed the performance leap with the same data without the pageset..

I'll look up the nice link you submitted  ;)

[blom0344]

Just a little bit of feedback (perhaps as reference): We solved the performance issues by drastically reducing the amount of open connections. The model made extensive use of lookups to another database for code translation. Unfortunately all against the same table. With some additional tweaking (shifting the lookups to different part of the model etc) connections were reduced from 140 to 35 and performance (with pageset!!) went back from 5-10 minutes to around  15 seconds..

[cognosun]

Hi blom0344,

Can you please explain what you did to increase performance in simple terms.

'lookup's' , 'code translation', 'open connections' how are these words related to master-detail ?

[blom0344]

They are not directly related to Cognos and the report design.  Unfortunately the report was defined against an operational database whereas all lookups for codetranslation where stored elsewhere. For some reason Cognos opened up a really large number of database sessions. Simplifying the model reduced these numbers with about 70% and report performance was back on track..