SQL Server - convert complicated view to table - the best way 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.
I think the main problem here is that you're trying to use a single view to feed many different reports, thus complicating the view definition over and over.

Not all the reports will need all the columns, the calculations and the JOINs in that view, so, in many situations, you will be hitting tables and performing calculations for no reason.

A more sensible approach would be to have a separate, optimized, query for each report and have the bare minimum tables involved. I know that this sounds like a lot of work, but code reuse is really difficult to achieve in T-SQL without giving up performance.

That said, if all your reports are based on more or less the same data, an indexed view or a materialized intermediate table could be a viable solution. Indexed views have some limitations that make them impractical in some contexts (no self joins, no outer joins, no UNIONs etc...) and may introduce an unacceptable write overhead, but are really powerful when used correctly.