Meter Data Management System(MDMS) is an integrated system that manages electricity data such as electricity generation, transmission, and consumption in real time. Often, complex and varied queries with multiple join and selection operations need to be run on such data. Several studies have focused on improving the performance of query evaluation for such data by applying machine learning techniques to query optimization problems. However, these studies are limited to processing queries for data in a single environment. In this paper, we propose two types of Proximal Policy Optimization (PPO)-based join order optimization model on Spark SQL to improve the performance of retrieval of large amounts of data.
The first PPO-based model uses cost as a reward. This model using cost adopts the cost computation method of Spark SQL to train with the costs of the join plans generated by the model as rewards. The model can find more join plans with lower costs than the plans that Spark SQL finds because Spark SQL is limited to a low search space. We demonstrate that the proposed PPO-based optimization model generates join plans with similar or lower costs than Spark SQL without executing the optimization algorithm of Spark SQL. However, because the cost computation method of Spark SQL that first model utilizes estimates the join plans using the statistics of input tables, it can be inaccurate. So, we propose a second model that uses more accurate metric. The second PPO-based model uses actual execution time as a reward. This model is trained with the execution time of the join plans generated by the model as rewards. This model can find join plans that take less time than Spark SQL. We use the actual execution time to make the model more accurate than the first model. We demonstrate that the second proposed PPO-based model generates join plans with similar or less execution times than Spark SQL.