Mousa submits a question asking about how to determine the best method to update tables supporting a materialized view.
I have a project to reduce the maintenance cost of materialized views. Can you help me how to choose between recompilation ( recreates materialized view from scratch) and refresh materialized views when there is change on the source data?
The issue is what has the least cost?
- Drop the materialized view, update the underlying data, and then recreate the materialized view
- Update the underlying data, and allow the materialized view to update during the udpates
The answer is like most things in SQL Server; it depends. Worse yet, there isn’t any magic formula to predetermine the best method. Updating a few records it is generally faster not to drop and re-create the view. Updating a significant amount of data, re-building the view is often faster.
In my experience, you have to try it both ways, track time and cost, and compare the results. Sometimes you must also try different sizes of data to determine the threshold where it may make sense to change from one method to the other.
Another technique, I have found works well when working with large sets of data, is to split a large set into many smaller sets, and process each one separately. This method works extremely well when working with a limited amount of memory in your SQL instance. It allow more work to be performed without swapping.
If you have other tips you’d like to add to the conversation, just leave a comment, or drop an Email to btaylor@sswug.org.
Cheers,
Ben