Alex Rivera | Logout

Improving performance on a view with a LOT of joins

Asked 2009-12-17T21:11:06.153
9

I have a view that uses 11 outer joins and two inner joins to create the data. This results in over 8 million rows. When I do a count (*) on the table it takes about 5 minutes to run. I'm at a loss as to how to improve the performance of this table. Does anyone have any suggestions on where to begin? There appear to be indexes on all of the columns that are joining (though some are composit, not sure if that makes a difference...)

Any help appreciated.

sql-server join performance left-join
Edit
Report

1 Answer

1

A few things you could consider:

  1. denormalisation. Reduce the number of joins required by denormalising your data structure
  2. partitioning. Can you partition data from large tables? e.g. a large table, could perform better if partitioned into a number of smaller tables. Enterprise Edition from SQL 2005 onwards has good support for partitioning, see here. Would consider this if you start getting in the realms of 10s/100s of millions of rows
  3. index management/statistics. Are all indexes defragged? Are statistics up to date?
answered 2009-12-17T21:20:14.303

Your Answer