I have round about 5 tables which each have more than 1,000,000 datasets inside of them.
I`ve read about indexed-views and that they can increase the performance at continuous querys. But the tables/the data are continuous updated respectively the statement behind the view just returns a fraction of the datasets time to time (sometimes every hour, but it is possible that it changes every day or week, it fluctuates).
Its not smart to create an index on each of these tables, cause the data will grow and grow and the index will be bigger than the data itself (just kidding but its really not smart in this scenario)
So what is your advice for me to reach my goal to create a view which is performant enough to beat the normal statement each time I need the data.
This is not a definite answer, rather just some general ideas to consider.
An important piece of information is what indexes the 5 underlying tables have. If those indexes are helpful for the view, then the query optimizer would use them, thus an index on the view might not make the view faster.
Based on just the information you're giving us, the main questions are the business needs, which would help to determine your database architecture: how often the tables are updated, how often the view is queried, how fast the updates and view query need to perform, and how current the view data needs to be.
If the view query performance is more important than the speed of the table updates, then use an indexed view.
If the view data can be cached and not current, and if the table update speed is important, then you could periodically copy all of the table data into a reporting table, such as once per day or hour. On that reporting table, you could create an index for fast read querying.
If the view performance is not important, but that view data has to be current, and if table update speed is important, then you probably have to use a non-indexed view, and maybe rely on table scans for every view query.