The view I'm building is getting more complicated and bigger every week. The idea for it was to have a source for a number of multiple reports for management, including ad-hoc queries done by some of them (Accountants - what can I say). I'm not worried about the load on the server - this one is dedicated just for that, so it will be a while until any serious resources got used. But it's taking longer and longer to do anything on it.
It's about 30% calculated fields, has at the moment with 25 joins of which I can drop maybe 8, but there will be more added at some point. No way to make it indexed, as it's spanning 4 different databases (for now), so performance is tortoisery at best.
So I'm thinking to dump the contents of the view into a table on schedule and the question is: is there a better way? And if not, what obstacles to look out for?
EDIT: Staleness of data: up to previous day. Data range: all rows, unfortunately, with current count 3.2 million. Also, there is the issue of the development phase - right now we're working out what we need, want and desire, so the performance tuning is pointless ATT. Indexing would be a good start, but not right now on this view. Split into multiple views/queries sounds good, but will not cut down execution time in any meaningful sense (down 20 minutes sounds great until you realize that starting point is 75 minutes).
Data I'm using is already copied from other databases(and servers) via SSIS package. Reworking it to consolidate data in one database might be doable, but that's not my jurisdiction. This may involve financial costs and until all the dev work is finalized will be rejected by management.
All I know that I need to speed things up considerably and fast. May be dirty. Unless I'll spend more time waiting for queries to execute than on development proper.