I introduced Materialized Views as a Resilience technique, but referenced Microsoft’s version of a Materialized view with the intention of saying that an SQL Materialized View does not enhance Resilience in an application. Enhancing resilience is obtained by having different services fulfill applications requests. When one service fails, it is possible for the other services to continue to perform.
If you create your own materialized view in a completely different data store, then if your OLTP storage becomes unavailable the view continues to be available. This works if your materialized view is done using any other data storage than your OLTP SQL Database.
In contrast, if you choose to optimize performance of your application you can do that with a materialized view in the OLTP database. In SQL Server they way this is done is by defining the view with Schema Binding. Secondly, to Materialize the view, you create a unique index on the view.
Since you used Schema Binding it makes it so you can’t change the table structure of any of the other tables referenced in the view.
In my presentation of Materialized View Shahid found confusion, He writes:
Very confusing topic from day one – materialized views (oracle) / indexed views (sql server).
First of all we need to know how these indexed views are updated when DML operation applied to any under laying table(s) is view is updated like
- a total refresh of the view like drop or re-create
- a trigger like mechanism update the indexed view like we normally do to update summary tables i.e. update summary_table set summary_column = summary_column – old value + new value
if first case is applied (drop and then re-create) then its total waste. if second case followed then why its taking to much time on busy OLTP system.
Since you create a unique index on the view, the index is updated with each update to any of the underlying table(s). So, depending on your view definition, it is possible for a change of a single row in an underlying table to modify multiple rows of the view; possibly throusands or millions.
The updating of the view index is clearly not that of re-materializing the view. I have some Materialized Views based on millions of rows that take minutes to create. However, I can update underlying data is seconds or less. On an OLTP system, seconds may not be acceptable.
The point is, you probably created the Materialized View because the performance of joining the data real time was not sufficient. This means that each time you update the underlying data, you are re-materializing just the underlying rows based on the view definition. The difference is that you perform this work once when the data changes instead of each time you read the data.
Thanks, Shahid for the comments. I’m sure there were others who were confused as well.
If I didn’t make it clear this time, please leave a comment or drop an Email to btaylor@sswug.org.
Cheers,
Ben