Performance improvements using materialized views

When used in the right conditions, materialized views can significantly improve performance by precomputing expensive operations such as joins and storing the results in the form of a view that is stored on disk. The optimizer considers materialized views when deciding on the most efficient way to satisfy a query, even when the materialized view is not referenced in the query.

In designing your application, consider defining materialized views for frequently executed expensive queries or expensive parts of your queries, such as those involving intensive aggregation and join operations. Materialized views are designed to improve performance in environments where:

the database is large
frequent queries result in repetitive aggregation and join operations on large amounts of data
changes to underlying data are relatively infrequent
access to up-to-the-moment data is not a critical requirement

Consider the following requirements, settings, and restrictions before using a materialized view:

Disk space requirements Since materialized views contain a duplicate of data from base tables, you may need to allocate additional space on disk for the database to accommodate the materialized views you create. Careful consideration needs to be given to the additional space requirements so that the benefit derived is balanced against the cost of using materialized views.
Maintenance costs and data freshness requirements The data in materialized views needs to be refreshed when data in the underlying tables changes. The frequency at which a materialized view needs to be refreshed needs to be determined by taking into account potentially conflicting factors, such as:
- Rate at which underlying data changes Frequent or large changes to data render manual views stale. Consider using an immediate view if data freshness is important.
- Cost of refreshing Depending on the complexity of the underlying query for each materialized view, and the amount of data involved, the computation required for refreshing may be very expensive, and frequent refreshing of materialized views may impose an unacceptable workload on the database server. Additionally, materialized views are unavailable for use during the refresh operation.
- Data freshness requirements of applications If the database server uses a stale materialized view, it presents stale data to applications. Stale data no longer represents the current state of data in the underlying tables. The degree of staleness is governed by the frequency at which the materialized view is refreshed. An application must be designed to determine the degree of staleness it can tolerate to achieve improved performance.
- Data consistency requirements When refreshing materialized views, you must determine the consistency with which the materialized views should be refreshed.
Use in optimization You should verify that the optimizer considers the materialized views when executing a query. You can see the list of materialized views used for a particular query by looking at the Advanced Details window of the query's graphical plan in Interactive SQL.
You can also use Application Profiling mode in Sybase Central to determine whether a materialized view was considered during the enumeration phase of a query by looking at the access plans enumerated by the optimizer. Tracing must be turned on, and must be configured to include the OPTIMIZATION_LOGGING tracing type, to see the access plans enumerated by the optimizer.
Data-altering operations Materialized views are read-only; no data-altering operations such as, INSERT, LOAD, DELETE, and UPDATE, can be used on them.
Keys, constraints, triggers, and articles While you can create indexes on materialized views, you cannot create keys, constraints, triggers, or articles on them.

Performance improvements using materialized views

See also