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?
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..
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.
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..