I have a huge view what consists of about 15+ million rows and about 150+ columns. That view is made of a lot of LEFT OUTER JOIN between a master table and various related tables.
That master table contains a list of products with a unique product id, but the view is a cross product of the products and all their attributes contained in the external tables.
We have an application that needs to materialize that view applying some business logic to the dataset. What the application does is go through each product, collect the rows from that view and retrieve the attributes of each column through a SELECT DISTINCT query and then, after everything is computed, the result gets written back to a row in a new table.
Of course this is a very slow operation because we run a SELECT DISTINCT for each column, multiplied by each row of the view.
Is there any other way to manage such a huge view? I've been thinking about splitting it up by a certain number of rows or by columns, but I'm not even sure that would make any difference. Are there best practices to work around this issue? Thanks!