We have one report which runs about 160 times each week in Subscription.
Each report instance takes approximately 3 hours to run, with some running 5 hours.
This is not acceptable.
I decided to look into the report guts.
The actual main report query runs in less than 30 seconds.
There are 4 sub-reports.
Each report is about 300-500 pages.
The report must be exported to PDF.
I searched online for solutions.
Found an MSDN article that I read a a few months ago but re-read more of the details.
The article recommended a few things:
- Remove page counts and page # of ## from the footer
- Remove Date Time Global variables
- Remove images
- Change the default text box "Can Grow" setting from True to False
It didn't mention this but I added "with (nolock)" to all the tables in the From Clause.
Doing these simple things, the run/export time reduced tremendously.
Let me know if you have other suggestions to really speed this up!