Page set performance

[jmwhite]

I have a report in which I am using a page set. If I run the query itself using Run > View Tabular Data, the entire result set returns in about 3 minutes. However, when I run the report with the page set, it is taking over an hour to complete.

Has anyone else experienced a similar issue and is there something that can be done to improve the performance?

[blom0344]

The issue is with the set that you return. Page sets seem to work okay if you need to display 5-20 pages, but if you need many more performance is likely to crumble. The tabular set can be produced as a flatfile with minimal processing from the server.  However, if it takes already 3 minutes to produce a tabular output, then either you have a slow performing query or a mass of data..

[jmwhite]

Only 6 to 10 pages are generated depending on the particular dataset the query is run over. The query is complicated. However, I'm still not understanding the immense difference between the 3 minutes to return data and the 60-70 minutes to format the report.

[blom0344]

Hard to give you an advice, but I would start from low load generating 1-2 pages incrementing in steps of 1 page (using filters) Is performance linear with the load or does it collapse somewhere halfway? Make sure to check server performance / traces on the database to find what is going on..