Disadvantages of Materialized View
- It is not possible to create Materialized View under any Materialized View i.e. Materialized View cannot be nested.
- Materialized View cannot be created on the tables which are direct source of streaming inserts.
- The SQL query which is used to create Materialized View cannot be altered once the view is created.
Query Performance Optimization
- Aggregated data
- Filtered data
- Joined data
Aggregated data: Whenever you perform aggregation on a large data set, So to optimize the performance you can create Materialized View on the aggregated data that will improve the query performance.
Filtered data: Let’s understand this by an example assume you have 1 million records and you only want to analyze 1 lakh records every time .So create a Materialized View on top of the base table by filtering the required data which will reduce the cost of scanning the whole data and increase the efficiency whenever you use Materialized View.
Joined data: In Bigquery to improve query performance create a Materialized View on subqueries which includes heavy joins and taking long time in scanning the database. So if you use Materialized View it will drastically reduce the cost and improve the query performance.
What Is Materialized View In Big Query ?
A materialized view is a precomputed snapshot of data in BigQuery, which stores the data physically from the output of a query onto the disk. It automatically refreshes the data from its base table periodically, ensuring the data remains up-to-date with changes to its underlying base tables. They are faster as compared to logical views because of their significant performance.
Materialized View overcomes the need to fetch data from the base tables every time the query is executed. Instead, the precomputed data stored in the view can be quickly accessed, resulting in faster query execution and efficiency.
Base Table : A base table is a Bigquery table where the actual data resides.
Contact Us