Efficiently updating materialized views

Rated 3.87/5 based on 641 customer reviews

Query processing can be sped up by keeping frequently accessed users' views materialized.However, the need to access base relations in response to queries can be avoided only if the materialized view is adequately maintained.The tables,views or materialized views used in FROM clause of the query are termed as master table and database where these objects exist are called materialized view.Refer following for general syntax of materialized view creation.Obviously, other magic, like using the view indirectly would not work either. First of all, if you expect to support different editions of SQL Server backend, you should keep this behavior and noexpand hint in mind.Obviously optimization technique for 3rd party applications would not work either.Even if it sounds almost the same as the regular views, indexed views are completely different animals.That type of the views are not only about the abstraction but more about performance. As you can see the situation is dramatically improved. Now let’s run the original statement in Enterprise edition of SQL Server and see the plan.

Unfortunately this is not the case with other editions but Enterprise and Developer.

Let’s see that: With the standard edition of SQL Server it does not even use “materialized” data by default.

If you want to force SQL to use the view data, you have to use (noexpand) hint.

Last thing I’d like to show is performance implications. As you can see, it introduces nice performance hit because of the view support.

Similar to the indexes – you have to pay the price of view maintenance for the benefit of performance improvements. Hard to say especially if you have heavy loaded OLTP system.

Leave a Reply