Designing Summary Tables to Optimize Performance in Relational Databases: An Empirical Approach
DOI:
https://doi.org/10.15294/edukom.v13i1.41416Keywords:
Database Performance, RDBMS, Table PartitioningAbstract
The performance of transactional applications involving databases will decline over time due to the increasing amount of stored data and the continuously rising volume of transactions. This research proposes a summary table design method that integrates table partitioning techniques and data aggregation to improve query efficiency without sacrificing the consistency and accuracy of the stored data. We tested this method using 30,275 transaction records on Google Cloud infrastructure and MySQL Server 8.0, demonstrating a data processing speed increase of up to 92.36% compared to conventional table designs. The research results show that this method not only accelerates data processing but also simplifies data management in relational database management systems (RDBMS). This method is relevant for transactional database applications with high transaction loads, such as the financial and e-commerce sectors. By combining the physical table design architecture and efficient query processing, this research contributes to the development of more scalable, reliable, and suitable database designs for high workload scenarios.
References
Acharya, S., Ghadge, K., Ranjan, B. S. C., Devadula, S., & Chakrabarti, A. (2020). Evaluating the effectiveness of InDeaTe tool in supporting design for sustainability. Artificial Intelligence for Engineering Design, Analysis and Manufacturing, 34(1), 45–54. https://doi.org/10.1017/S0890060419000337
Albert, A., & Bozza, C. (2016). The Relational Database System of KM3NeT. In Epj Web of Conferences. https://doi.org/10.1051/epjconf/201611607004
Azhir, E., Jafari Navimipour, N., Hosseinzadeh, M., Sharifi, A., & Darwesh, A. (2021). A technique for parallel query optimization using MapReduce framework and a semantic-based clustering method. PeerJ Computer Science, 7, e580. https://doi.org/10.7717/peerj-cs.580
Bt Wan Abu Bakar, W. A., Abdullah, Z. B., Md Saman, Md. Y. B., Abd Jalil, M. M. B., Man, M. B., & Herawan, T. (2015). Vertical Association Rule Mining: Case study implementation with relational DBMS. 2015 International Symposium on Technology Management and Emerging Technologies (ISTMET), 279–284. https://doi.org/10.1109/ISTMET.2015.7359044
Carroll, N. (2017). The Routledge Companion to Accounting Information Systems (M. Quinn & E. Strauss, Eds.). Routledge. https://doi.org/10.4324/9781315647210
Castro-Medina, F., Rodríguez-Mazahua, L., López-Chau, A., Cervantes, J., Alor-Hernández, G., Machorro-Cano, I., & Arrioja-Rodríguez, M. L. (2022). A New Method of Dynamic Horizontal Fragmentation for Multimedia Databases Contemplating Content-Based Queries. Electronics, 11(2), 288. https://doi.org/10.3390/electronics11020288
Chandrashekariah, Y. A. B., & H. A., D. (2024). Structured query language query join optimization by using rademacher averages and mapreduce algorithms. Bulletin of Electrical Engineering and Informatics, 13(3), 1730–1740. https://doi.org/10.11591/eei.v13i3.6837
Kim, K., Kim, S.-M., Park, Y., Lee, E., Jung, S., Kang, J., An, D., Min, K., Shim, S. R., Yu, H. W., & Han, H. W. (2024). A blockchain-based healthcare data marketplace: prototype and demonstration. JAMIA Open, 7(2). https://doi.org/10.1093/jamiaopen/ooae029
Kumar, K., & Azad, S. K. (2017). Database normalization design pattern. 2017 4th IEEE Uttar Pradesh Section International Conference on Electrical, Computer and Electronics (UPCON), 318–322. https://doi.org/10.1109/UPCON.2017.8251067
Lee, A., Zait, M., Cruanes, T., Ahmed, R., & Zhu, Y. (2009). Validating the Oracle SQL engine. Proceedings of the Second International Workshop on Testing Database Systems, 1–7. https://doi.org/10.1145/1594156.1594161
Lee, Y. T. (1999). An overview of information modeling for manufacturing systems integration. https://doi.org/10.6028/NIST.IR.6382
Liu, Z., & Idreos, S. (2016). Main Memory Adaptive Denormalization. Proceedings of the 2016 International Conference on Management of Data, 2253–2254. https://doi.org/10.1145/2882903.2914835
Loand, C.-M., & Hung, H.-Y. (2014). Towards a UML Profile to Relational Database Modeling. In Applied Mathematics & Information Sciences. https://doi.org/10.12785/amis/080233
Luo, X., Huo, X., & Fu, L. (2019). Query Optimization of Data Based on Window Function and Distributed Cluster in Visual Academic Search System. Shanghai Jiaotong Daxue Xuebao/Journal of Shanghai Jiaotong University, 53(8), 978–982.
Ma, C. X., & Wang, Y. M. (2013). Application of the Standardization of the Relationship in the Database Design. Applied Mechanics and Materials, 401–403, 1809–1812. https://doi.org/10.4028/www.scientific.net/AMM.401-403.1809
Mendoza, D., & Piedra, N. (2020). TutNorBD: Assistant for teaching and learning process of relational database normalization up to 3NF from a universal table. 2020 XV Conferencia Latinoamericana de Tecnologias de Aprendizaje (LACLO), 1–8. https://doi.org/10.1109/LACLO50806.2020.9381184
Mulyawan, B., Vionelsy, & Sutrisno, T. (2020). Product recommendation system on building materials shopping using FP-Growth algorithm. IOP Conference Series: Materials Science and Engineering, 1007, 012144. https://doi.org/10.1088/1757-899X/1007/1/012144
Rodrigues, R. A., Filho, L. A. L., Gonçalves, G. S., Mialaret, L. F. S., da Cunha, A. M., & Dias, L. A. V. (2018). Integrating NoSQL, Relational Database, and the Hadoop Ecosystem in an Interdisciplinary Project involving Big Data and Credit Card Transactions (pp. 443–451). https://doi.org/10.1007/978-3-319-54978-1_57
Samidi, S., Suladi, R. Y., & Lesmana, A. B. (2022). Implementation of Database Distributed Sharding Horizontal Partition in MySQL. Case Study of Application of Food Serving On Kemkes. JURNAL SISFOTEK GLOBAL, 12(1), 50. https://doi.org/10.38101/sisfotek.v12i1.477
Santosa, A., Rinaldy, A. M., & Andriani, D. (2020). Database Design for Distribution Simulation Game. IOP Conference Series: Materials Science and Engineering, 879(1), 012179. https://doi.org/10.1088/1757-899X/879/1/012179
Shah, B., Pareek, J., & Kanziya, D. (2018). A Novel Approach to Optimize Subqueries for Open Source Databases (pp. 331–346). https://doi.org/10.1007/978-981-10-6916-1_31
Sug, H. (2020). A Method for Normalization of Relation Schema Based on Data to Abide by the Third Normal Form. WSEAS TRANSACTIONS ON MATHEMATICS, 19, 216–225. https://doi.org/10.37394/23206.2020.19.20
Suryana, A., Supendi, Y., & Yulianto, E. (2019). Designing software management work unit of research and development department of public works and public housing. International Journal of Advanced Science and Technology, 28(6), 55–61.
TAŞYÜREK, M. (2022). A Novel Approach to Improve the Performance of the Database Storing Big Data with Time Information. Balkan Journal of Electrical and Computer Engineering, 10(4), 388–396. https://doi.org/10.17694/bajece.1059070
Wang, F. Q., Liu, Y., & Han, Q. L. (2014). Research on Improving Data Query Speed Methods. Applied Mechanics and Materials, 556–562, 5825–5828. https://doi.org/10.4028/www.scientific.net/AMM.556-562.5825
