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!

  • Post an execution plan, so we can look where the heavy lifting is going on. – Kin Shah May 15 '14 at 18:29
  • If changes are infrequent, is it practical to materialize the view to table as and when the underlying tables are updated? – Mark Storey-Smith May 15 '14 at 19:53
  • Check out Jeff Moden's articles on efficient and dynamic CrossTab queries (sqlservercentral.com/articles/T-SQL/63681 and sqlservercentral.com/articles/Crosstab/65048) and ensure that your SQL code to generate the CrossTab is employing best practices. – Pieter Geerkens Jul 27 '14 at 17:31
  • Reduce the number of queries. Is it really necessary to execute one query per row and column? – usr Jul 27 '14 at 23:09

It'll sound a little stupid, but creating a separate composite non clustered index on the attributes table on product id and each column, resulting in 150+ indexes on the attributes table, will get you the ultimate speed for that query because it will support the select distinct column value by product from the attributes table. Your insert performance will be super painful.

  • actually you're dead on about the non clustered indexes. They helped in querying the huge view. We also moved part of our procedure to using the tables behind the view instead of the view itself so that materializing the view to a condensed table now takes 40x less the original time. – Valerio Santinelli May 20 '14 at 15:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.