Query cost estimation and development of an application for processing resource-intensive queries

Rapid growth of stored data volume necessitates integration of tools for monitoring, analysis and optimization of database queries for timely and correct identification of the most resource-intensive queries. These circumstances determine the relevance of developing software tools for assessing the causes of slow queries with the formation of various optimization options. This paper examines the reasons influencing the resource-intensive queries of data sampling. The reasons for slow queries are shown, such as the quality of collected statistics, use of indexes, hints, query structure, correctness of database initialization parameter settings, as well as possible solutions to the identified causes. The study is interesting from the point of view of explaining the basics of the physical operations provided by the query execution subsystem, which interprets the procedural plan of query execution to optimize the cost. To solve the problem of speeding up slow queries based on a correct procedural plan, we propose the development of an application that takes into account the composition of the analyzed cost, volume and time characteristics of queries to optimize them. The results of testing the developed system, which helps to improve the performance of queries, are described. The speed of query execution was evaluated by the following metrics: data access operation, expression cost, I/O operation cost, CPU time, time spent on processing the whole sample. Performing experiments to evaluate the correctness of identifying slow queries confirms the feasibility of applying in practice the results of the conducted research and the developed application.

1. Belattar S., Abdoun O., El khatir H. New learning approach for unsupervised neural networks model with application to agriculture field. International Journal of Advanced Computer Science and Applications. 2020;11(5):360–369.

2. Kim W. On optimizing an SQL-Like Nested Query. ACM Transactions on Database Systems (TODS). 1982;7(3):443-469.

3. Lukichev M., Barashev D. XML query algebra for cost-based optimization. SYRCODIS*07 The Fourth Spring Young Researchers Colloquium on Databases and Information Systems. 2007. URL: http://ceur-ws.org/Vol-256 (дата обращения: 21.09.2023).

4. Maher, M., Wang, J. Optimizing queries in extended relational databases. In: Ibrahim M., Küng J., Revell N. (eds) Database and Expert Systems Applications. DEXA 2000. Lecture Notes in Computer Science. 2000;1873. DOI: 10.1007/3-540-44469-6_36.

5. May N., Moerkotte G. Normalization and translation of XQuery. Advanced Applications and Structures in XML Processing: Label Streams, Semantics Utilization and Data Query Technologies. Hershey, Igi Global Publishing; 2010. 500 p. DOI: 10.4018/978-1-61520-727-5.

6. Kuznecov S.D., Mendkovich N.A. Optimizacija konjunktov uslovij v sostave zaprosov. Modelirovanie i analiz informacionnyh sistem. 2011;18(3):144–154.. (In Russ.).

7. Nurmatova E.V., Gusev V.V., Kotliar V.V. Analysis of the features of the optimal logical structure of distributed databases. Selected Papers of the 8th International Conference "Distributed Computing and Grid-technologies in Science and Education", 2018;2267:579–584. URL: https://ceur-ws.org/Vol-2267. (дата обращения: 21.09.2023).

8. Novikov B.A., Gorshkova E.A., Grafeeva N.G. Osnovy tehnologij baz dannyh. 2-e izd. Moscow, DMK Press; 2020. 582 p. (In Russ.).

9. Borchuk L.E. Sovershenstvovanie processa nastrojki zaprosov pol'zovatelej na osnove asimptoticheskih ocenok zatrat resursov. Informacionnye tehnologii. 2008;6:6–11. (In Russ.).

10. Borchuk L.E., Kuz'min A.A. Ocenka vremeni vypolnenija zaprosa v reljacionnoj SUBD na osnove asimptoticheskih modelej zatrat resursov. Naukoemkie tehnologii. 2008;4:61–64. (In Russ.).

11. Dombrovskaja G., Novikov B., Bejlikova A. Optimizacija zaprosov v PostgreSQL. Moscow, DMK Press; 2022. 278 p. (In Russ.).

12. Zajcev E.I., Nurmatova E.V. O podhode k upravleniju znanijami i razrabotke mul'tiagentnoj sistemy predstavlenija i obrabotki znanij. Russian Technological Journal. 2023;11(4):16–25. DOI: 10.32362/2500-316X-2023-11-4-16-25. (In Russ.).

13. Kuznecov S.D., Mendkovich N.A. Novye algoritmy leksicheskoj optimizacii zaprosov. Modeli i analiz informacionnyh sistem. 2009;16(4):22–33. (In Russ.).

14. Mendkovich N.A., Kuznecov S.D. Optimizacija kon#junktov uslovij v sostave zaprosov. Modeli i analiz informacionnyh sistem. 2011;18(3):144–154. (In Russ.).

15. Mendkovich N.A. Ob jeffektivnosti minimizirujushhego podhoda k optimizacii zaprosov. Modelirovanie i analiz informacionnyh sistem. 2016;23(2):153–163. DOI: 10.18255/1818-1015-2016-2-153-163 (In Russ.).

16. Mosin S.V., Zykin S.V. Kjeshirovanie zaprosov k reljacionnoj baze dannyh s ispol'zovaniem oblastej istinnosti. Modelirovanie i analiz informacionnyh sistem. 2015;22(2):248–258. DOI: 10.18255/1818-1015-2015-2-248-258. (In Russ.).

17. Nurmatova E.V. Analiz procedurnogo plana sql-zaprosa. Opornyj obrazovatel'nyj centr. Innopolis. 2021;2:116–121. (In Russ.).

18. Ivanov O. Mashinnoe obuchenie dlja planirovanija zaprosov. Otkrytye sistemy. SUBD; 2016. URL: https://www.osp.ru/os/2016/01/13048650 (data obrashhenija: 21.09.2023) (In Russ.).

19. Pashinin O.V. Optimizacija zaprosov k bazam dannyh. Matematicheskie struktury i modelirovanie. 2017;17:100–107. (In Russ.).

20. Hajlan A.M., Pol'shhikov K.A., Algazali S.M.M. Obnaruzhenie resursoemkih zaprosov k bazam dannyh na osnove primenenija samoorganizujushhihsja kart i nechetkogo vyvoda. Jekonomika. Informatika. 2021:48(3):578–593. DOI: 10.52575/2687-0932-2021-48-3-578-593. (In Russ.).

21. Wang S., Rundensteiner E.A., Mani M. Optimization of nested xquery expressions with orderby clauses. Data & Knowledge Engineering. 2007;60(2):303–325. DOI: 10.1016/j.datak.2006.03.004.