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

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.