Report run time - queries run fast but report runs very slow

[psrpsrpsr]

I have a report for which the queries feeding crosstabs runs in about 1 minute, but the full report takes almost 7 hours to run.

The report design is:
Top 20%:
Crosstab 1 | Crosstab 2 | Crosstab3

Bottom 20%:
Crosstab 1 | Crosstab 2 | Crosstab3

There are maybe 20 rows per top/bottom section. There are page sets based upon the region of each location.

Why would a query that takes 1 minute to run take 7 hours to render to Excel? Are there ways I can improve the performance? This runtime is unacceptable for business users.

Thank you!

[bdbits]

How elaborate is the format? Is it HTML output or something like Excel? How many page sets are there? How big is the total result set underlying the report?

One thing you can do is make a copy of the report, then start simplifying and outright eliminating sections. If the performance suddenly improves, you've found the problem.

There is also a report profiler that gives pretty detailed information, but you will need an admin to help you run it. I am in a rush at the moment so don't have a link, search the board here and you may be able to find info on invoking it (edit a file to start it, run report, re-edit file to stop, look at log).