I´ve heard about indexed-views in MSSQL which can increase the performance. My problem is that I want to create a view which joins round about 5 tables. The tables are constantly updated and are essential for the backend.

But the customer should have a view where he has all the data which is interesting for him. Cause the normal statement is really slowly, I thought about creating an indexed view to increase performance, when getting data from view.

My problem is that I have to create an unique clustered index which has to updated each time the data behind is changed.

I don´t know which is smarter, to create a new indexed-view or run the normal statement. I can not create more indices inside the base tables cause this will not be smart and it would not work great.

The following points are important for me:

  • the view query performance must be fast
  • the view data must be current
  • the base table update speed must be fast

Is there any idea or advice you can share with me?

Thanks to anyone who can help!

  • 1
    Hi, Sharing the execution plan via pastetheplan would be helpful. An indexed view could certainly be an option, but without an actual query plan it is hard to say what is best for you. – Randi Vertongen Apr 19 '19 at 8:53
  • 1
    Whether or not the performance improvements gained with the indexed view (or other indexes) outweigh the costs of index maintenance and concurrency depend on your workload. – Dan Guzman Apr 19 '19 at 10:17
  • 1
    Indexed views aren’t magical spells that make all queries against views faster. They are intended for a very specific use case and the speed improvement relies on the indexed view’s ability to drastically reduce the number of reads required to produce the end result (typically through massive aggregation). Most of the time the way to fix a view is NOT to put an index on it, but rather to focus on the indexes on the table(s) involved in the view. I'd also be careful about generalizing things like "more indexes will not be smart and it would not work great." How do you know this? – Aaron Bertrand Apr 19 '19 at 13:07
  • Following on Aaron's comment, I have to add: have you tested one method versus the other to see if performance is better with an indexed view? – Randolph West Apr 23 '19 at 3:22

Your Answer

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

Browse other questions tagged or ask your own question.