A Controlled Evaluation of Relational Database Models for Query Performance across DAS, RAID-0, and RAID-1 Storage Configurations
DOI:
https://doi.org/10.53799/zb6b1375Keywords:
Database Design, Query Optimization, Relational Database Model, Cost-Based Optimizer, Query PerformanceAbstract
Relational database performance is shaped not only by query formulation and storage configuration, but also by the underlying schema design. This study evaluates three alternative relational database models (ERD 1, ERD 2, and ERD 3) for the same inventory-oriented domain under three storage configurations (DAS, RAID 0, and RAID 1). The experiment covers seven query categories (simple, aggregate, inner join, outer join, subquery, correlated subquery, and complex) and four data volumes (100,000; 1,000,000; 10,000,000; and 100,000,000 records per entity). Performance is assessed using two complementary indicators: mean measured response time (seconds) and Oracle 11gR2 cost-based optimizer estimates (optimizer cost). Each test was executed in 10 repeated runs with full result-set fetching, clearing the buffer cache between runs using ALTER SYSTEM FLUSH BUFFER_CACHE, and the mean response time was recorded. At 100,000,000 records per entity, ERD 3 shows substantially lower mean response time than ERD 2 for heavy workloads across storage configurations; for aggregate queries, mean response time decreases from 140.413 s (ERD 2) to 8.487 s (ERD 3) on DAS, from 246.191 s to 25.441 s on RAID 1, and from 130.066 s to 4.157 s on RAID 0, while inner-join response time decreases from 64.890 s to 18.308 s on DAS, from 95.133 s to 24.792 s on RAID 1, and from 38.655 s to 8.019 s on RAID 0. Optimizer-cost outputs at 100,000,000 records show a consistent reduction for ERD 3 in the same categories; for aggregate queries, cost decreases from 608,290 (ERD 2) to 44,620 (ERD 3) on DAS, from 457,459 to 44,320 on RAID 1, and from 457,425 to 4,431 on RAID 0. Overall, the results suggest that the ERD 3 specialization-based design tends to reduce execution time and optimizer-estimated cost for aggregation- and join-intensive workloads under the tested HDD-based environment.
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. DOI: 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. DOI: 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. DOI: 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. DOI: https://doi.org/10.1145/235968.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. DOI: 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. DOI: 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. DOI: 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. DOI: 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. DOI: https://doi.org/10.1016/j.is.2025.102636
[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. DOI: 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. DOI: 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. DOI: 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. DOI: https://doi.org/10.1109/DEXA.2011.60
[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. DOI: 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. DOI: 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. DOI: 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. DOI: https://doi.org/10.1145/971701.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. DOI: 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. DOI: 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. DOI: 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.
