Predictive SQL Query Tuning Using Sequence Modeling of Query Plans for Performance Optimization
DOI:
https://doi.org/10.63282/3050-9416.IJAIBDCMS-V3I2P111Keywords:
SQL Query Optimization, Predictive Query Tuning, Sequence Modeling, Query Execution Plans, Machine Learning for Databases, Performance OptimizationAbstract
Modern database management systems are increasingly required to process complex analytical SQL queries under dynamic workloads and evolving data distributions. Conventional cost-based query optimizers rely on static statistics and heuristic-driven models that often fail to accurately predict execution behavior, resulting in suboptimal query plans and performance variability. This paper presents a predictive SQL query tuning approach that formulates query optimization as a sequence modeling problem over query execution plans. By representing logical and physical query plans as structured operator sequences, the proposed framework captures contextual dependencies among operators that significantly influence execution performance. Advanced sequence learning models, including recurrent neural networks and Transformer-based architectures, are employed to learn performance-aware representations from historical query workloads. The trained models are used to predict query latency and plan efficiency, enabling proactive plan re-ranking and targeted tuning actions such as join reordering, access path selection, and optimizer hint generation prior to execution. Extensive experiments conducted on standard benchmarks, including TPC-H and real-world analytical workloads, demonstrate that the proposed approach achieves low prediction error and consistently improves query execution performance compared to native cost-based optimizers and learned cardinality estimators. Results show notable reductions in average and tail query latency, improved robustness under data skew and better generalization to complex query plans. The findings highlight the effectiveness of sequence-based learning in augmenting traditional database optimization pipelines and provide a scalable foundation for intelligent, self-tuning database systems
References
1. Akdere, M., Çetintemel, U., Riondato, M., Upfal, E., & Zdonik, S. B. (2012, April). Learning-based query performance modeling and prediction. In 2012 IEEE 28th International Conference on Data Engineering (pp. 390-401). IEEE.
2. Marcus, R., & Papaemmanouil, O. (2019). Plan-structured deep neural network models for query performance prediction. arXiv preprint arXiv:1902.00132.
3. Kamatkar, S. J., Kamble, A., Viloria, A., Hernández-Fernandez, L., & Cali, E. G. (2018, June). Database performance tuning and query optimization. In International Conference on Data Mining and Big Data (pp. 3-11). Cham: Springer International Publishing.
4. Malik, S. U. R., Khan, S. U., Ewen, S. J., Tziritas, N., Kolodziej, J., Zomaya, A. Y., ... & Li, H. (2016). Performance analysis of data intensive cloud systems based on data management and replication: a survey. Distributed and Parallel Databases, 34(2), 179-215.
5. Fox, G. C., Glazier, J. A., Kadupitiya, J., Jadhao, V., Kim, M., Qiu, J., … & Beckstein, O. (2019). Learning Everywhere: Pervasive Machine Learning for Effective High Performance Computation. arXiv. arXiv:1902.10810.
6. Zhao, D., Zhang, Z., Zhou, X., Li, T., Wang, K., Kimpe, D., ... & Raicu, I. (2014, October). Fusionfs: Toward supporting data-intensive scientific applications on extreme-scale high-performance computing systems. In 2014 IEEE international conference on big data (Big Data) (pp. 61-70). IEEE.
7. Venkatraman, V., Dimoka, A., Pavlou, P. A., Vo, K., Hampton, W., Bollinger, B., ... & Winer, R. S. (2015). Predicting advertising success beyond traditional measures: New insights from neurophysiological methods and market response modeling. Journal of Marketing Research, 52(4), 436-452.
8. Adikari, A., Burnett, D., Sedera, D., De Silva, D., & Alahakoon, D. (2021). Value co-creation for open innovation: An evidence-based study of the data driven paradigm of social media using machine learning. International Journal of Information Management Data Insights, 1(2), 100022.
9. Gao, L., Song, J., Liu, X., Shao, J., Liu, J., & Shao, J. (2017). Learning in high-dimensional multimedia data: the state of the art. Multimedia Systems, 23(3), 303-313.
10. Najafabadi, M. M., Villanustre, F., Khoshgoftaar, T. M., Seliya, N., Wald, R., & Muharemagic, E. (2015). Deep learning applications and challenges in big data analytics. Journal of big data, 2(1), 1.
11. Georgiou, T., Liu, Y., Chen, W., & Lew, M. (2020). A survey of traditional and deep learning-based feature descriptors for high dimensional data in computer vision. International Journal of Multimedia Information Retrieval, 9(3), 135-170.
12. Li, M., & Wang, Z. (2020). Deep learning for high-dimensional reliability analysis. Mechanical Systems and Signal Processing, 139, 106399.
13. Viglas, S. D. (2014, March). Just-in-time compilation for SQL query processing. In 2014 IEEE 30th International Conference on Data Engineering (pp. 1298-1301). IEEE.
14. Lan, H., Bao, Z., & Peng, Y. (2021). A survey on advancing the dbms query optimizer: Cardinality estimation, cost model, and plan enumeration. Data Science and Engineering, 6(1), 86-101.
15. Sharma, M., Singh, G., & Singh, R. (2019). A review of different cost-based distributed query optimizers. Progress in Artificial Intelligence, 8(1), 45-62.
16. Tan, R., Chirkova, R., Gadepally, V., & Mattson, T. G. (2017, December). Enabling query processing across heterogeneous data models: A survey. In 2017 IEEE International Conference on Big Data (Big Data) (pp. 3211-3220). IEEE.
17. Ganguly, S., Hasan, W., & Krishnamurthy, R. (1992, June). Query optimization for parallel execution. In Proceedings of the 1992 ACM SIGMOD international conference on management of data (pp. 9-18).
18. Ren, G., Ni, X., Malik, M., & Ke, Q. (2018, April). Conversational query understanding using sequence to sequence modeling. In Proceedings of the 2018 World Wide Web Conference (pp. 1715-1724).
19. Singhal, R., & Nambiar, M. (2016, July). Predicting SQL query execution time for large data volume. In Proceedings of the 20th International Database Engineering & Applications Symposium (pp. 378-385).
20. He, Q., Jiang, D., Liao, Z., Hoi, S. C., Chang, K., Lim, E. P., & Li, H. (2009, March). Web query recommendation via sequential query prediction. In 2009 IEEE 25th international conference on data engineering (pp. 1443-1454). IEEE.
