THE METHOD OF OPTIMIZING SQL QUERIES OF THE DATABASE MANAGEMENT SYSTEM
DOI:
https://doi.org/10.26906/SUNZ.2023.2.151Keywords:
databases, SQL query optimization, indexes, IN operatorsAbstract
Context. The size of a database, which refers to the volume of stored data, can vary significantly. It is clear that the larger the size of the database, the more time is required to search for the necessary information, resulting in increased query processing time for the server. On one hand, this problem can be addressed by improving the performance of the computers hosting the database management systems (DBMS). However, simply enhancing computer performance is insufficient; often, much better results can be achieved by changing the algorithms for processing SQL queries. Therefore, despite the fact that optimization of SQL queries has been conducted for decades, due to the rapid accumulation of information and the workload on database servers, such work has become even more relevant. Objective. The main objective of this work is to improve the speed of executing incoming queries in a relational database, ensuring high performance and user convenience. This will be accomplished by developing an enhanced optimization method that synthesizes complex SQL queries from a large number of simpler queries, thereby increasing overall efficiency and usability. Method. This article presents a method for optimizing the synthesis of complex SQL queries from numerous simple queries, which enhances the speed of executing the incoming query by a relational database while simultaneously ensuring high performance and ease of use. Results. A method for optimizing SQL queries specifically for situations where data retrieval speed deteriorates over time has been developed. This method involves replacing the IN operator with a temporary table and utilizing a non-clustered index. Consequently, it accelerates the data retrieval process by reducing logical references. Conclusions. The main research objectives were identified and successfully achieved: the analysis of existing approaches to SQL query optimization and the fundamental principles of query optimizers as components of DBMS were conducted, a method for optimizing SQL queries was developed, and the effectiveness of the proposed enhanced method was evaluated.Downloads
References
Rupley, M. L. (2008). Introduction to query processing and optimization. Indiana University, South Bend, South Bend, IN, USA, TechReport TR–20080105–1.
Bhajipale, R., Bisen, P., Meshram, A., & Thakur, S. S. (2016). SQL tuner. International Journal of Computer Trends and Technology, 33(1), 29–32.
Karthik, P., Reddy, G. T., & Vanan, E. K. (2012). Tuning the SQL query in order to reduce time consumption. International Journal of Computer Science Issues, 9(4/3), 418–423.
Habimana, J. (2015). Query optimization techniques – tips for writing efficient and faster SQL queries. International Journal of Scientific & Technology Research, 4(10), 22–26.
Sahal, R., Nihad, M., Khafagy, M. H., & Omara, F. A. (2018). iHOME: Index based JOIN query optimization for limited big data storage. Journal of Grid Computing, 16(2), 345–380.
Sharma, M. (2012). Query optimization using SQL transformations. International Journal of IT, Engineering and Applied Sciences Research, 1(1), 100–104.
Srinivas, S. S., Naik, B. V., & Kumar, J. S. A. (2017). Query minimization methods. International Journal of Scientific & Engineering Research, 8(5), 30–33.
Patel, D., & Patel, P. (2015). An approach for query optimization by using schema object base view. International Journal of Computer Applications, 119(16), 21–24.
Patil, S., Damare, P., Sonawane, J., & Maitre, N. (2015). Study of performance tuning techniques. Journal of Emerging Technologies and Innovative Research (JETIR), 2(3), 499–502.
Corlatan, C. G., Lazar, M. M., Luca, V., & Petricica, O. T. (2014). Query optimization techniques in Microsoft SQL server. Database Systems Journal, 5(2), 33–48.
Lokhande, A. D., & Shete, R. M. (2012). The use of hints in SQL–Nested query optimization. Journal of Data Mining and Knowledge Discovery, 3(1), 54–57.
Brent Ozar Unlimited. (2023). How to Download the Stack Overflow Database via BitTorrent. Retrieved April 3, 2023, from https://www.brentozar.com/archive/2015/10/how-to-download-the-stack-overflow-database-via-bittorrent/.
Єрмолаєв, О. Д. Метод оптимізації SQL запитів : дипломна робота … бакалавра : 172 Телекомунікації та радіотехніка / Єрмолаєв Олександр Дмитрович. – Київ, 2021. – 56 с.
