Analysis of Query Optimization Using Deep Reinforcement Learning Using Particle Swarm Optimization Algorithms

Karthikeyan M P; Krishnaveni K

Author(s)

Karthikeyan M P Department of Computer Science, Sri S.Ramasamy Naidu Memorial College, (Affiliated to Madurai Kamaraj University,Madurai), Sattur, Tamilnadu,India

Krishnaveni K Department of Computer Science, Sri S. Ramasamy Naidu Memorial College, (Affiliated to Madurai Kamaraj University,Madurai), Sattur, Tamilnadu,India

Abstract

Query optimization is a well-studied problem in the database industry, with numerous solutions proposed over the last several decades. The success of deep reinforcement learning (DRL) has generated new opportunities in query optimization. One of the most difficult tasks in query optimization and query plan generation is determining the order in which join operations between tables are done (i.e. relations). Even if the final results of a query remain identical regardless of join order, the order in which the tables of a query are joined can have a significant impact on query execution time. Deep reinforcement learning, in particular a data-driven method to reasoning about enumeration heuristics, provides a novel algorithmic viewpoint on join enumeration. We must now control what training data the model views and how that data is featured, rather than the standard tunable parameters of a query optimizer. The algorithm makes few assertions about the cost model's structure or the search space's topology. We demonstrate that Q learning optimizes plans well across many different cost models for a small set of training queries. On the TPC-H database, the Query Optimization Algorithms Q-Learning and PSO (Particle Swarm Optimization) are assessed. During the evaluation, the optimizer failed to complete one query within the maximum time permitted, whereas the deep reinforcement learning-based models (QLearning) and heuristics model (PSO) managed. Of course, the standard join ordering problem is NP-hard, and practical algorithms use heuristics to make the search for a good plan efficient. A novel method for Query Optimization using Particle Swarm Optimization (QOPSO) and Deep Q Learning (DQL) for parameter tuning of Join Operation Cost and Processing Cost is suggested in this paper.

Publication Details

Page(s) 333-345

DOI DOI not available

Published Journal: International Journal of Communication Networks and Information Security, Volume: 16, Issue: S1, Year: 2024

Request the full text from PASTIC

Keywords

Deep learning, Deep Neural Networks (DNNs), Recurrent Neural Networks (RNNs) and Feature Extraction, Ensemble Models

References

Wang . J.,Elmasri R.,Eltabakh M. .2021 ."Query Optimization Techniques for NoSQL Databases,". 2021 IEEE International Conference on Big Data (Big Data), : 4965-4970.

Zhou .2021 ."Automatic Query Optimization: A Survey,". IEEE Transactions on Knowledge and Data Engineering, 33(2) : 432-450.

Marcus ]. R.,Papaemmanouil O. .2018 .Deep reinforcement learning for join order enumeration. In International Workshop on Exploiting Artificial Intelligence Techniques for Data Management, : .

Tripathi . A. K.,Shukla A.,Kumar R. .2021 ."Performance Evaluation of DBMS Using Different Benchmarks,". 2021 International Conference on Advances in Computing, Communication and Control (ICAC3), : 1-6.

Zeng J.,Wang X.,Liu X. .2021 .". DBMS Performance Optimization Based on Machine Learning," 2021 IEEE 37th International Conference on Data Engineering (ICDE), : 2045-2048.

Joshi . B. V.,Rajendran M. A. .2021 ."Performance Analysis of SQL Server Using Index Optimization Techniques,". 2021 International Conference on Advances in Computing and Data Sciences (ICACDS), : 1-6.

Singh . V. K.,Singh A. K.,Upadhyay D. K. .2021 ."SQL Server Performance Tuning: A Comprehensive Approach,". 2021 IEEE International Conference on Computing, Communication and Automation (ICCCA), : 1-5.

Sharma . A.,Ghosh R.,Ranjan R. .2022 ."Benchmarking TPC-H on Cloud Platforms: A Comparative Study,". 2022 IEEE International Conference on Cloud Computing Technology and Science (CloudCom), : 1-8.

Zhao Y.,Zheng L.,Wu J. .2021 ."TPC-H Benchmark Testing for Distributed Database Systems,". 2021 IEEE 37th International Conference on Data Engineering (ICDE), : 1-8.

TPC-H Benchmark ,. M. K. Srivastava A. K.,Sharma A. K. .2021 ."Performance Tuning and Optimization in PostgreSQL: A Case Study,". 2021 International Conference on Computational Intelligence and Communication Technology (CICT), : 1-6.

Gupta S.,Kumar A. .2021 ."PostgreSQL Optimization Techniques for Big Data Workloads,". 2021 IEEE International Conference on Big Data (Big Data), : 1-8.

Xu Z.,Sun W.,Liu Y. .2022 ."Reinforcement Learning Based Intelligent Control Systems: A Survey,". IEEE Transactions on Neural Networks and Learning Systems, 33(4) : 1386-1406.

Marcus ]. R.,Papaemmanouil O. .2018 .Deep reinforcement learning for join order enumeration. In Proceedings of the First International Workshop on Exploiting Artificial Intelligence Techniques for Data Management, page 3. ACM, : .

Zou L.,He Z.,Zhang Z. .2022 ."Q-Learning Based Task Offloading Optimization in Vehicular Edge Computing,". 2022 IEEE International Conference on Communications Workshops (ICC Workshops), : 1-6.

Liu Y.,Yang Z.,Zhang Z.,Xu Y. .2022 ."Q-Learning Based Congestion Control for Wireless Multimedia Sensor Networks,". IEEE Transactions on Wireless Communications, 21(5) : 3285-3296.

Li C.,Yao X.,Zhang Z.,Chen J. .2021 ."Approximability of NP-Hard Optimization Problems,". IEEE Transactions on Computers, 70(12) : 2195-2206.

. P. K. R. Madduri R. K. S.,Gorthi S. N.,Sivanandam S. N. .2021 ."Efficient Heuristic Solutions for NP-Hard Problems Using Genetic Algorithms,". 2021 IEEE Congress on Evolutionary Computation (CEC), : 1-8.

Shah S. N.,Joshi M. A. .2021 ."Selfish Algorithm Based Approach for Distributed System Optimization,". 2021 IEEE 10th International Conference on Communication Systems and Network Technologies (CSNT), : 1-5.

Vo T. M.,Prakash P. R. .2022 ."A New Hybrid Genetic Algorithm for Solving Multi - objective Optimization Problems,". 2022 IEEE Congress on Evolutionary Computation (CEC), : 1-6.

Li X.,He J.,Wong M. L. .2022 ."Ant Colony Optimization for Dynamic Optimization: A Survey,". IEEE Transactions on Evolutionary Computation, 26(4) : 710-723.

Ghosh M.,Dutta S.,Saha S. N. .2022 ."Enhanced Particle Swarm Optimization Algorithm for Multi-objective Problems,". 2022 IEEE Congress on Evolutionary Computation (CEC), : 1-8.

.2022 ."Adaptive Particle Swarm Optimization Algorithm for Global Optimization,". IEEE Transactions on Cybernetics, 52(1) : 450-462.

Li M.,Zhang H.,Zheng Z.,Zhao Y. .2022 ."Deep Q-Learning Based Resource Allocation for Network Slicing in 5G Wireless Networks,". 2022 IEEE Global Communications Conference (GLOBECOM), : 1-6.

Zhang K.,Ma H.,Wang Z. .2022 ."Adaptive Deep Q-Learning for Resource Management in Dynamic Network Slicing,". IEEE Transactions on Mobile Computing, 21(1) : 158-170.