A lesser known term that does not get complete attention during performance tuning exercises is Query Materialization.
In simple terms, Query Materialization refers to the data sets that are generated during a SQL statement execution and stored in physical memory until the said query is executed in full. This temporarily materialized data is then nullified and memory is released for subsequent processes. It can be seen as a temporary table being created with required values and dropped at latter point in time.
Consider for example a SQL Query as follows
SELECT * FROM TABLE_A WHERE COL_X IN (SELECT X FROM TABLE_B WHERE Z > 1000)
In this case, the results from the sub-query (non-correlated) is materialized before the main query is executed. Similarly, query materialization also happens in other scenarios such as, which are not restricted to the following scenarios alone
- Usage of In-Line views
- Usage of WITH clause or similar constructs
- Calculations in the SQL result set
- Several complex JOINS
In order to get details of data being materialized during query execution, one can investigate the explain plan. It may not be straightforward because each database management system will have its own internal engine to handle SQL statements.
Large & Complex queries that refer to sizable sets of data will utilize large amounts of memory to store such intermediate results due to Query Materialization, which results in direct impact of performance of the query as well as the system as a whole.
While designing a Query or Fine tuning query performance, one should consider Query Materialization as an integral part in order achieve optimal performance.
NOTE: Query Materialization should not be equated with data caching. Both concepts are different and are handled at different stages
SAP BW offers a property that can be enabled and disabled as required for each and every individual query. This property should be used with caution because it can cause spike in resource usage in the system.