Performance Optimization of ERD Designs Using Cost-Based Optimization for Large-Scale Query Processing

Juanda Hakim Lubis; Sri  Handayani; Herman  Mawengkang; Fajrul Malik Aminullah  Napitupulu

doi:10.52436/1.jutif.2025.6.3.4523

Authors

Juanda Hakim Lubis Information Technology, Universitas Pembinaan Masyarakat Indonesia, Indonesia
Sri Handayani Information Technology, Universitas Pembinaan Masyarakat Indonesia, Indonesia
Herman Mawengkang Computer Science, Universitas Sumatera Utara, Indonesia
Fajrul Malik Aminullah Napitupulu Department of Computer Science, Institute Modern of Architecture and Technology, Indonesia

DOI:

https://doi.org/10.52436/1.jutif.2025.6.3.4523

Keywords:

Cost-Based Optimizer, Database Design, Query Optimization, Query Performance, Relational Database Model

Abstract

The rapid growth of stored data, particularly on magnetic disks, is doubling annually for each department within a company, creating a pressing need for efficient database management. While database design is a fundamental step in establishing a high-performance system, it alone is insufficient to ensure optimal efficiency. Query optimization plays a critical role in improving data transaction speed, reducing query execution time, and enhancing overall system responsiveness. This study evaluates various relational database models under different data volumes to analyze their impact on query performance. Using the Cost-Based Optimizer method and access time measurements, we assess query costs and determine the factors influencing performance. The results indicate that among the three database models analyzed, ERD-3 consistently delivers superior performance, especially in handling complex queries. This is attributed to its modular structure, strategic indexing, and reduced full table scans, which collectively minimize query execution costs. Additionally, several key factors significantly affect query performance, including record count, attribute size, query complexity, primary and unique key usage, indexing strategies, order-by clauses, index sequences, and SQL function application. This research contributes to the field of database optimization by demonstrating that ERD structuring and cost-based query analysis significantly improve system efficiency in large-scale environments. These findings emphasize the necessity of a well-structured, scalable database model and efficient query processing techniques to accommodate large-scale data growth. The study’s conclusions provide a foundation for advanced optimization strategies, ensuring that modern database systems remain efficient and adaptable to evolving data demands.

Downloads

Download data is not yet available.

References

Y. Jani, “Optimizing database performance for large-scale enterprise applications,” Int. J. Sci. Res. (IJSR), vol. 11, no. 10, pp. 1394–1396, 2022. https://doi.org/10.21275/SR24716121211.

H. Singh, “Adaptive search optimization: Dynamic algorithm selection and caching for enhanced database performance,” Int. J. Comput. Appl., vol. 182, no. 31, pp. 25–32, 2023. https://doi.org/10.13140/RG.2.2.34751.69281.

J. Lao et al., “GPTuner: A manual-reading database tuning system via GPT-guided Bayesian optimization,” Proc. VLDB Endow., vol. 17, no. 8, pp. 1939–1952, Apr. 2024. https://doi.org/10.14778/3659437.3659449.

D. Lindner, D. Ritter, and F. Naumann, “Enabling data dependency-based query optimization,” Proc. VLDB Endow., vol. 17, no. 6, pp. 1025–1037, 2024. https://doi.org/10.48550/arXiv.2406.06886.

J. Ba and M. Rigger, “CERT: Finding performance issues in database systems through the lens of cardinality estimation,” in Proc. ACM SIGMOD Int. Conf. Manage. Data, 2023, pp. 1600–1612. https://doi.org/10.1145/3597503.3639076.

T. Gautier, S. Nagar, N. Agrawal, dan A. Kumar, “Hybrid Transactional/Analytical Graph Processing in Modern Memory Hierarchies,” in Proc. Scalable Data Manag. Systems for Future Hardware (Lecture Notes in Computer Science), no. 13531, Singapore, Jan. 2025, pp. 91–116. https://doi.org/10.1007/978-3-031-74097-8_4.

V. B. Ramu, “Optimizing database performance: Strategies for efficient query execution and resource utilization,” Int. J. Comput. Trends Technol., vol. 71, no. 7, pp. 15–21, 2023. https://doi.org/10.14445/22312803/IJCTT-V71I7P103.

H. Song, W. Zhou, H. Cui, X. Peng, and F. Li, “A survey on hybrid transactional and analytical processing,” Data Sci. J., vol. 23, no. 1, pp. 45–67, Jun. 2024, https://doi.org/10.1007/s00778-024-00858-9.

J. Li, Q. Wang, S. Han, and P. P. C. Lee, “The Design and Implementation of a High-Performance Log-Structured RAID System for ZNS SSDs,” arXiv preprint arXiv:2402.17963, Feb. 2024. https://doi.org/10.48550/arXiv.2402.17963.

G. Gottlob, M. Lanzinger, D. M. Longo, C. Okulmus, R. Pichler, and A. Selzer, “Structure Guided Query Evaluation: Towards Bridging the Gap from Theory to Practice,” arXiv preprint arXiv:2303.02723, Mar. 2023. https://doi.org/10.48550/arXiv.2303.02723.

Z. Yang, “Machine learning for query optimization,” Ph.D. dissertation, Univ. California, Berkeley, CA, USA, 2020. ISBN: 979-8-3529-5188-0.

A. R. Raipurkar and M. B. Chandak, “Optimized execution method for queries with materialized views: Design and implementation,” J. Intell. Fuzzy Syst., vol. 41, no. 6, pp. 6191–6205, 2021. https://doi.org/10.3233/JIFS-202821.

R. Marcus and T. Kraska, “OneShotSTL: One-Shot Seasonal-Trend Decomposition For Online Time Series Anomaly Detection And Forecasting,” Proc. VLDB Endow., vol. 16, no. 6, pp. 1432–1444, Feb. 2023, https://doi.org/10.14778/3583140.3583155.

M. Gonthier, D. D. Sanchez-Gallegos, H. Pan, B. Nicolae, S. Zhou, H. D. Nguyen, V. Hayot Sasson, J. G. Pauloski, J. Carretero, K. Chard, dan I. Foster, “D Rex: Heterogeneity Aware Reliability Framework and Adaptive Algorithms for Distributed Storage,” arXiv preprint arXiv:2506.02026, May 2025. https://doi.org/10.48550/arXiv.2506.02026.

B. Ding, R. Zhu, and J. Zhou, “Learned Query Optimizers”, Foundations and Trends® in Databases, vol. 9, no. 3, pp. 173–247, 2024. https://doi.org/10.1561/1900000082.

A. Margara, G. Cugola, N. Felicioni, and S. Cilloni, “A model and survey of distributed data intensive systems,” ACM Comput. Surv., vol. 56, no. 1, Article 1, pp. 1–71, Jun. 2023. https://doi.org/10.1145/3604801.

R. Heinrich, M. Luthra, J. Wehrstein, H. Kornmayer, and C. Binnig, “How good are learned cost models, really? Insights from query optimization tasks,” Proc. on arXiv, Feb. 2025, https://doi.org/10.48550/arXiv.2502.01229.

A. Mhedhbi, A. Deshpande, and S. Salihoğlu, “Modern Techniques for Querying Graph Structured Databases,” Foundations and Trends® in Databases, vol. 14, no. 2, pp. 72–185, Oct. 2024. https://doi.org/10.1561/1900000090.

A. Kipf et al., “Learned cardinalities: Estimating correlated joins with deep learning,” in Proc. Conf. Innov. Data Syst. Res. (CIDR), Jan. 2019. https://doi.org/10.48550/arXiv.1809.00677.

T. Kraska et al., “The case for learned index structures,” in Proc. ACM SIGMOD Int. Conf. Manage. Data, Jun. 2018, pp. 489–504. https://doi.org/10.1145/3183713.3196909.

N. Vasilenko, A. Demin, and D. Ponomaryov, “Adaptive Cost Model for Query Optimization,” arXiv preprint arXiv:2409.17136, Sep. 2024. https://doi.org/10.48550/arXiv.2409.17136.

R. Marcus et al., “Neo: A learned query optimizer,” Proc. VLDB Endow., vol. 12, no. 11, pp. 1705–1718, Jul. 2019. https://doi.org/10.14778/3342263.3342644.

Y. Ji, D. Amagata, Y. Sasaki, and T. Hara, “PolyCard: A learned cardinality estimator for intersection queries on spatial polygons,” Journal of Intelligent Information Systems, vol. 63, pp. 873–891, Jan. 2025. https://doi.org/10.1007/s10844-025-00921-z.

X. Wang, M. L. Sapino, W.-S. Han, A. El Abbadi, G. Dobbie, Z. Feng, Y. Shao, and H. Yin, “Database Systems for Advanced Applications: 28th International Conference,” DASFAA 2023, Tianjin, China, April 17–20, 2023, Proceedings, Part I. Singapore: Springer, 2023. https://doi.org/10.1007/978-3-031-30637-2.

A. Kamali, V. Kantere, C. Zuzarte, and V. Corvinelli, “Robust plan evaluation based on approximate probabilistic machine learning,” arXiv preprint arXiv:2401.15210, Jan. 2024, https://doi.org/10.48550/arXiv.2401.15210.

Z. M. Khalid and S. R. M. Zeebaree, “Big data analysis for data visualization: A review,” Int. J. Sci. Bus., vol. 5, no. 2, pp. 64–75, 2021, https://doi.org/10.5281/zenodo.4481357.

R. Zhu, W. Chen, B. Ding, X. Chen, A. Pfadler, Z. Wu, and J. Zhou, “Lero: A learning‑to‑rank query optimizer,” Proc. on arXiv, Feb. 2023, https://doi.org/10.48550/arXiv.2302.06873.