I am creating a massive view with around 250 columns. Let me call it consolidated_View This view is created by joining 11 views on a single key which is an int. I need to be sending data in this view to few business users on daily basis
A select * from the consolidated_View takes around 6 hrs to populate data which is tooo much. To reduce the time I created a job that physicalizes the 11 views into tables. Now the consolidated_View takes 4 hours which is also a lot. I would love to physicalize the consolidated_View but that would take too much space which I don’t have.
Any suggestions what I can do to just be able to get the data on daily basis without taking so long
The view is massive. It generates around 4546764 rows daily. I tried to look at the execution plan but could not find much clue. I am not very efficient in reading execution plans. But As i said i am doing a joining on a single key which is an integer
not really. Actually I have physicallized ony 6 of the 11 views instead of phisicallizing them all. These were the ones which took longest while running individually. If i have to create index on all the joining tables and views i will have to create indexed views.. is that right??