For the last 3 years I have been building and expending a report generator for a client. In that time I've produced about 1700+ individual reports which have run hundreds of times if not more. In that time I have learned a number of interesting things...
- templates and macros are good
- parameters and recursion are even better
- uploading to some storage like AWS and emailing links instead of content
- concurrency and DB capacity is a serious challenge
- replication is going to cause issues
- reporting engine in proximity to the DB server causes latency and other costs
- reporting engine capacity
What I'm fighting with is trying to get management to scale up the systems so that we can get more done in shorter periods. As time progresses we are putting more reports in production and they are reporting on more data all of which takes longer and risks failing due to memory or timeout constraints.
This also falls back on the challenge of not paying for idle hardware.