1

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!

Improve this question
4
  • Post an execution plan, so we can look where the heavy lifting is going on.
    – Kin Shah
    Commented May 15, 2014 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
    Commented May 15, 2014 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
    Commented Jul 27, 2014 at 17:31
  • Reduce the number of queries. Is it really necessary to execute one query per row and column?
    – usr
    Commented Jul 27, 2014 at 23:09

1 Answer 1

Reset to default
1

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.

Improve this answer
1
  • 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
    Commented May 20, 2014 at 15:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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