Database Optimization Techniques with Logic Execution Optimization on Microservices Architecture

Authors

DOI:

https://doi.org/10.31154/cogito.v9i1.444.60-72

Keywords:

Optimisation Techniques, Indexing, Microservices Architecture

Abstract

Microservices architecture, a distributed framework architecture that allows changes to one module without interfering with other modules. The implementation of this architecture has its own challenges. The get-list-attachment API running on this architecture takes an average of 12.5 seconds to serve data. This needs to be considered because business processes require shorter access times to support decision making. The research objective is to obtain query response time efficiency for accounting applications. To achieve this, the research uses database optimization techniques with logic execution optimization microservices architecture. This study obtained the source of information from the Accounting Harmony Accounting Module, which has an API (get-list-attachment) with data sourced from Service Accounting (581253 records) and Service Users (2182 records). Based on a series of tests carried out, several services need to be added with APIs to improve the microservices architecture to accept bulk parameters that generate a list of objects so that data presentation is more optimal. After doing a series of engineering on microservices architecture and indexing application, query response time performance increased by 49.22% for Service Accounting module.

References

Arteta Albert, N. Gómez Blas, and L. F. de Mingo López, “Intelligent Indexing—Boosting Performance in Database Applications by Recognizing Index Patterns,” Electronics, vol. 9, no. 9, p. 1348, Aug. 2020, doi: 10.3390/electronics9091348.

E. Inersjö, Comparing database optimisation techniques in PostgreSQL : Indexes, query writing and the query optimiser. 2021. Accessed: Jun. 27, 2022. [Online]. Available: http://urn.kb.se/resolve?urn=urn:nbn:se:kth:diva-306703

Samidi, D. Iskandar, M. Fachruroji, W. Adi Septyo Wibowo, and A. Khaerani A, “Database Tuning in Hospital Applications Using Table Indexing and Query Optimization,” J. Pendidik. Tambusai, vol. 6, no. 1, pp. 1960–1967, 2022.

C. V. Dave, “Microservices Software Architecture: A Review,” Int. J. Res. Appl. Sci. Eng. Technol., vol. 9, no. 11, pp. 1494–1496, Nov. 2021, doi: 10.22214/ijraset.2021.39036.

C. E. da Silva, Y. de L. Justino, and E. Adachi, “SPReaD: service-oriented process for reengineering and DevOps: Developing microservices for a Brazilian state department of taxation,” Serv. Oriented Comput. Appl., vol. 16, no. 1, pp. 1–16, Mar. 2022, doi: 10.1007/s11761-021-00329-x.

S. Maesaroh, H. Gunawan, A. Lestari, M. SufyanAts Tsaurie, and M. Fauji, “Query Optimization in MySQL Database Using Index,” Nternational J. Cyber IT Serv., vol. 2, no. 2, pp. 104–110, 2022.

M. V. Praveena and A. A. Chikkamannur, “IndexingStrategies for Performance Optimization of Relational Databases,” Int. Res. J. Eng. AndTechnologyIRJET, vol. 8, no. 5, pp. 3801–3805, 2021.

S. Samidi, F. Fadly, Y. Virmansyah, R. Y. Suladi, and A. B. Lesmana, “Optimasi Database dengan Metode Index dan Partisi Tabel Database Postgresql pada Aplikasi E-Commerce. Studi pada Aplikasi Tokopintar,” J. Pendidik. Tambusai, vol. 6, no. 1, pp. 2094–2102, 2022.

E. Azhir, N. Jafari Navimipour, M. Hosseinzadeh, A. Sharifi, and A. Darwesh, “A technique for parallel query optimization using MapReduce framework and a semantic-based clustering method,” PeerJ Comput. Sci., vol. 7, p. e580, Jun. 2021, doi: 10.7717/peerj-cs.580.

M. Eslami, V. Mahmoodian, I. Dayarian, H. Charkhgard, and Y. Tu, “Query batching optimization in database systems,” Comput. Oper. Res., vol. 121, p. 104983, Sep. 2020, doi: 10.1016/j.cor.2020.104983.

A. Rahmanto, A. Budi, and R. Primananda, “Implementasi Self-Tuning Pada Database Dengan Menggunakan Metode Nesterov Accelerated Gradient,” J. Pengemb. Teknol. Inf. Dan Ilmu Komput., vol. 5, no. 5, pp. 1907–1913, 2021.

K. T. Hidayat, R. Arifudin, and A. Alamsyah, “Genetic Algorithm for Relational Database Optimization in Reducing Query Execution Time,” Sci. J. Inform., vol. 5, no. 1, p. 27, May 2018, doi: 10.15294/sji.v5i1.12720.

R. Marcus et al., “Neo: A Learned Query Optimizer,” 2019, doi: 10.48550/ARXIV.1904.03711.

W. K. G. Assunção, J. Krüger, and W. D. F. Mendonça, “Variability management meets microservices: six challenges of re-engineering microservice-based webshops,” in Proceedings of the 24th ACM Conference on Systems and Software Product Line: Volume A - Volume A, Montreal Quebec Canada, Oct. 2020, pp. 1–6. doi: 10.1145/3382025.3414942.

Q. Xie, W. Yang, and L. Yao, “A Database Optimization Strategy for Massive Data Based Information System,” in Proceedings of the 2019 2nd International Conference on Mathematics, Modeling and Simulation Technologies and Applications (MMSTA 2019), Xiamen, China, 2019. doi: 10.2991/mmsta-19.2019.47.

R. V. O’Connor, P. Elger, and P. M. Clarke, “Continuous software engineering-A microservices architecture perspective,” J. Softw. Evol. Process, vol. 29, no. 11, p. e1866, Nov. 2017, doi: 10.1002/smr.1866.

C. A. Győrödi, D. V. Dumşe-Burescu, R. Ş. Győrödi, D. R. Zmaranda, L. Bandici, and D. E. Popescu, “Performance Impact of Optimization Methods on MySQL Document-Based and Relational Databases,” Appl. Sci., vol. 11, no. 15, p. 6794, Jan. 2021, doi: 10.3390/app11156794.

S. J. Kamatkar, A. Kamble, A. Viloria, L. Hernández-Fernandez, and E. G. Cali, “Database Performance Tuning and Query Optimization,” in Data Mining and Big Data, vol. 10943, Y. Tan, Y. Shi, and Q. Tang, Eds. Cham: Springer International Publishing, 2018, pp. 3–11. doi: 10.1007/978-3-319-93803-5_1.

G. Feng, “The Design and Optimization of Database,” J. Phys. Conf. Ser., vol. 1087, p. 032006, Sep. 2018, doi: 10.1088/1742-6596/1087/3/032006.

J. Kossmann, T. Papenbrock, and F. Naumann, “Data dependencies for query optimization: a survey,” VLDB J., vol. 31, no. 1, pp. 1–22, Jan. 2022, doi: 10.1007/s00778-021-00676-3.

S.-V. KHOLOD, “Performance comparison for differenttypes of databases,” Fac. Appl. Sci. Ukr. Cathol. Univ., pp. 1–25, 2021.

M. S. Kumar and P. .J, “Comparison of NoSQL Database and Traditional Database-An emphatic analysis,” JOIV Int. J. Inform. Vis., vol. 2, no. 2, p. 51, Mar. 2018, doi: 10.30630/joiv.2.2.58.

Y. Y. Putra, O. Purwaningrum, and R. H. Winata, “PERBANDINGAN PERFORMA RESPON WAKTU KUERI MySQL, PostgreSQL, dan MongoDB,” J. Sist. Inf. Dan Bisnis Cerdas, vol. 15, no. 1, pp. 39–48, Mar. 2022, doi: 10.33005/sibc.v15i1.2749.

R. Wodyk and M. Skublewska-Paszkowska, “Performance comparison of relational databases SQL Server, MySQL and PostgreSQL using a web application and the Laravel framework,” J. Comput. Sci. Inst., vol. 17, pp. 358–364, Dec. 2020, doi: 10.35784/jcsi.2279.

D. Ilin and E. Nikulchev, “Performance Analysis of Software with a Variant NoSQL Data Schemes,” in 2020 13th International Conference “Management of large-scale system development” (MLSD), Moscow, Russia, Sep. 2020, pp. 1–5. doi: 10.1109/MLSD49919.2020.9247656.

P. Martins, M. Abbasi, and F. Sá, “A Study over NoSQL Performance,” in New Knowledge in Information Systems and Technologies, vol. 930, Á. Rocha, H. Adeli, L. P. Reis, and S. Costanzo, Eds. Cham: Springer International Publishing, 2019, pp. 603–611. doi: 10.1007/978-3-030-16181-1_57.

V. F. de Oliveira, M. A. de O. Pessoa, F. Junqueira, and P. E. Miyagi, “SQL and NoSQL Databases in the Context of Industry 4.0,” Machines, vol. 10, no. 1, p. 20, Dec. 2021, doi: 10.3390/machines10010020.

Downloads

Published

2023-06-30

How to Cite

Hadi Al Ghozali, I., Shiddiq Antarressa, M. ., & Samidi, S. (2023). Database Optimization Techniques with Logic Execution Optimization on Microservices Architecture . CogITo Smart Journal, 9(1), 60–72. https://doi.org/10.31154/cogito.v9i1.444.60-72