What a pain in the @$$. I have to create a sortable, searchable view that has nearly 100 columns, with over 65 joins, 36 “pivot columns” and more. How do I make this giant monolithic thing perform well?
Select the whole doggone thing into a new table, which you actually use to view the data.
SELECT * INTO New_Table FROM Big_VIEW
This is ONLY ok if the data is pretty much read only, since you need to add a trigger to the tables to update the New_Table on update of the actual records.
Nasty, but you can only create indexed views on very specific requirements, and this puppy needed Functions that did lookups, as well as Left Outer Joins. Tough.
