Evaluation Techniques of Optimization based on Materialized views in Decision Support Systems using PayOffRatio metrics

Abstract

Query optimization presents significant issues for the majority of decision support systems deployedin the world today. These systems require data warehouse structures to store and process data that should beused by online analytical processing (OLAP). Additionally, the volume, variety, and quick-changing nature of thedata in the data warehouse are essential, necessitating additional effort, research to keep the data current, and theleast expensive data updates. In this article, we propose a new metric called PayOffRatio based on the principleof view selection gain to address the problem of selecting materialized views in multidimensional data warehouses.The metric examines the response time of the materialized view relative to the execution time of its underlyingquery before view creation, taking into account the frequency of its use and its implementation time. The metricwas first evaluated using the standard greedy algorithm, where view selection depends only on the associatedmetric, and then using the greedy algorithm with dependencies between queries at adjacent levels, which is thestandard approach used in most schemas developed to improve the performance of decision support systems. Ourexperimental results confirm that generating the selection of materialized views using our PayOffRatio metric isthe most efficient. In addition, a comparison of our metric with other metrics used in the field will be presented.