Optimizing Database System Performance: Design and Query Optimization Strategies
DOI:
https://doi.org/10.53799/zb6b1375Keywords:
Database Design, Query Optimization, Relational Database Model, Cost-Based Optimizer, Query PerformanceAbstract
The amount of data stored in magnetic disks (e.g., floppy disks) increases by 100% each year for each department in a company, necessitating efforts to maintain an optimal database system. Designing a database is the initial step in creating a system with optimal performance. However, database design alone is not sufficient to enhance performance. One approach to improving data transaction speed is by optimizing query processing. This research evaluates different relational database models using varying amounts of data. Query costs are analyzed using the Cost-Based Optimizer method and access time measurements. The results of this study provide insights for database administrators in designing relational database models effectively and selecting appropriate query structures to optimize database performance. The findings indicate that: (1) database design can be optimized by separating entities based on specialized usage, and (2) factors such as record count, attribute size, query type, use of unique or primary keys, order-by clauses, index sequences, and SQL function usage significantly impact query cost and overall performance.References
[1] P. G. Selinger, M. M. Astrahan, D. D. Chamberlin, R. A. Lorie, and T. G. Price, “Access path selection in a relational database management system,” in Proc. ACM SIGMOD Int. Conf. Manage. Data, 1979, pp. 23–34, https://doi.org/10.1145/582095.582099.
[2] S. Chaudhuri, “An overview of query optimization in relational systems,” in Proc. ACM SIGACT-SIGMOD-SIGART Symp. Principles Database Syst. (PODS), 1998, pp. 34–43, https://doi.org/10.1145/275487.275492.
[3] G. Graefe, “Query evaluation techniques for large databases,” ACM Comput. Surv., vol. 25, no. 2, pp. 73–169, 1993, https://doi.org/10.1145/152610.152611.
[4] V. Poosala, Y. E. Ioannidis, P. J. Haas, and E. J. Shekita, “Improved histograms for selectivity estimation of range predicates,” in Proc. ACM SIGMOD Int. Conf. Manage. Data, 1996, pp. 294–305, https://doi.org/10.1145/233269.233342.
[5] V. Leis, A. Gubichev, A. Mirchev, P. Boncz, A. Kemper, and T. Neumann, “How good are query optimizers, really?” Proc. VLDB Endow., vol. 9, no. 3, pp. 204–215, 2015, https://doi.org/10.14778/2850583.2850594.
[6] H. Köhler and S. Link, “SQL schema design: Foundations, normal forms, and normalization,” in Proc. ACM SIGMOD Int. Conf. Manage. Data, 2016, pp. 267–282, https://doi.org/10.1145/2882903.2915239.
[7] G. L. Sanders and S. K. Shin, “Denormalization effects on performance of RDBMS,” in Proc. 34th Hawaii Int. Conf. Syst. Sci., 2001, https://doi.org/10.1109/HICSS.2001.926306.
[8] S. K. Shin and G. L. Sanders, “Denormalization strategies for data retrieval from data warehouses,” Decis. Support Syst., vol. 42, no. 1, pp. 267–282, 2006, https://doi.org/10.1016/j.dss.2004.12.004.
[9] D. Milićev, “Hyper-relations: A model for denormalization of transactional relational databases,” IEEE Trans. Knowl. Data Eng., 2021. Available: https://ieeexplore.ieee.org/document/9599377.
[10] D. Lindner, D. Ritter, and F. Naumann, “Enabling data dependency-based query optimization,” arXiv preprint
arXiv:2406.06886, 2024. Available: https://arxiv.org/abs/2406.06886.
[11] T. Taipalus, “On the effects of logical database design on database size, query complexity, query performance, and energy consumption,” arXiv preprint arXiv:2501.07449, 2025. Available: https://arxiv.org/abs/2501.07449.
[12] M. Fotache, M. I. Cluci, T. Taipalus, and G. Talaba, “The effects of database normalization on decision support system performance,” Inf. Syst., 2025. Available: https://www.sciencedirect.com/science/article/pii/S030643792500122X.
[13] J. Ba and M. Rigger, “CERT: Finding performance issues in database systems through the lens of cardinality estimation,” in Proc. IEEE/ACM 46th Int. Conf. Softw. Eng. (ICSE), 2024, https://doi.org/10.1145/3597503.3639076.
[14] J. Lao, Y. Wang, Y. Li, J. Wang, Y. Zhang, Z. Cheng, W. Chen, M. Tang, and J. Wang, “GPTuner: A manual-reading database tuning system via GPT-guided Bayesian optimization,” arXiv preprint arXiv:2311.03157, 2023. Available: https://arxiv.org/abs/2311.03157.
[15] S.-W. Lee, B. Moon, C. Park, J.-M. Kim, and S.-W. Kim, “A case for flash memory SSD in enterprise database applications,” in Proc. ACM SIGMOD Int. Conf. Manage. Data, 2008, pp. 1075–1086, https://doi.org/10.1145/1376616.1376723.
[16] S.-W. Lee, B. Moon, and C. Park, “Advances in flash memory SSD technology for enterprise database applications,” in Proc. ACM SIGMOD Int. Conf. Manage. Data, 2009, pp. 863–870, https://doi.org/10.1145/1559845.1559937.
[17] D. Bausch, I. Petrov, and A. Buchmann, “On the performance of database query processing algorithms on flash solid state disks,” in Proc. Int. Workshop Database and Expert Syst. Appl., 2011. Available: https://ieeexplore.ieee.org/document/6059807.
[18] Q. Xu, H. Siyamwala, M. Ghosh, T. Suri, M. Awasthi, Z. Guz, A. Shayesteh, and V. Balakrishnan, “Performance analysis of NVMe SSDs and their implication on real world databases,” in Proc. 8th ACM Int. Syst. Storage Conf., 2015, https://doi.org/10.1145/2757667.2757684.
[19] A. Fevgas, L. Akritidis, P. Bozanis, and Y. Manolopoulos, “Indexing in flash storage devices: A survey on challenges, current approaches, and future trends,” VLDB J., 2020, https://doi.org/10.1007/s00778-019-00559-8.
[20] G. Graefe, S. Harizopoulos, H. A. Kuno, and J. Shapiro, “Designing database operators for flash-enabled memory hierarchies,” IEEE Data Eng. Bull., vol. 33, no. 4, pp. 21–27, 2010. Available: http://sites.computer.org/debull/A10dec/A10DEC-CD.pdf.
[21] S. A. M. Tipan and R. B. Ricafort, “A performance analysis: Evaluating relational database system on HDD vs. SSD storage,” Int. J. Latest Technol. Eng. Manag. Appl. Sci., vol. 15, no. 4, 2026, https://doi.org/10.51583/IJLTEMAS.2026.150400049.
[22] D. A. Patterson, G. Gibson, and R. H. Katz, “A case for redundant arrays of inexpensive disks (RAID),” in Proc. ACM SIGMOD Int. Conf. Manage. Data, 1988, pp. 109–116, https://doi.org/10.1145/50202.50214.
[23] P. M. Chen, E. K. Lee, G. A. Gibson, R. H. Katz, and D. A. Patterson, “RAID: High-performance, reliable secondary storage,” ACM Comput. Surv., vol. 26, no. 2, pp. 145–185, 1994, https://doi.org/10.1145/176979.176981.
[24] P. M. Chen, E. K. Lee, G. A. Gibson, R. H. Katz, and D. A. Patterson, “Performance and design evaluation of the RAID-II storage server,” Distrib. Parallel Databases, vol. 2, no. 3, pp. 243–260, 1994, https://doi.org/10.1007/BF01266330.
[25] S. Manegold, P. Boncz, and M. Kersten, “Optimizing database architecture for the new bottleneck: Memory access,” VLDB J., vol. 9, no. 3, pp. 231–246, 2000, https://doi.org/10.1007/s007780000031.
Downloads
Published
Issue
Section
License
Copyright (c) 2026 AIUB Journal of Science and Engineering
This work is licensed under a Creative Commons Attribution-NonCommercial-NoDerivatives 4.0 International License.
AJSE contents are under the terms of the Creative Commons Attribution License. This permits anyone to copy, distribute, transmit and adapt the work non-commercially provided the original work and source is appropriately cited.
