Performance Optimisation of SQL Queries in Data Warehouses Enhancing SQL Query Performance through Indexing, Query Rewriting, and Materialised Views in Data Warehouses
Keywords:
SQL Query Optimisation, Data Warehouse Performance, Star Schema, Indexing Strategies, Query Rewriting Techniques, Materialised ViewsAbstract
With the rapid growth of data warehouses, efficient SQL query processing has become crucial for handling complex analytical workloads, particularly those involving large-scale joins, aggregations, and filtering operations. Poorly optimised queries can lead to excessive execution time, high I/O costs, and inefficient resource utilization, negatively affecting business intelligence systems. This study presents a comprehensive framework for SQL query optimisation in relational databases, combining indexing strategies, query rewriting techniques, materialised views, and execution plan analysis. The research utilizes a retail transaction dataset modeled in a star schema to simulate real-world analytical queries. Experimental evaluations were conducted to compare baseline and optimised queries across several performance metrics, including execution time, rows scanned, and optimiser cost. The results demonstrate significant performance improvements, including reduced query latency, fewer full table scans, and enhanced index utilisation. The use of materialised views provided near-instantaneous responses for repetitive aggregation tasks, while indexing and query rewriting enhanced the efficiency of join and filter operations. This study bridges the gap between theoretical optimisation concepts and practical implementation, offering a validated and integrated approach to SQL query optimisation for data warehouses.
Downloads
References
Adelusi, B. S., Ojika, F. U., & Uzoka, A. C. (2022). A conceptual model for cost-efficient data warehouse management in AWS, GCP, and Azure environments. International Journal of Multidisciplinary Research and Growth Evaluation, 3(2), 831-846.
Zhang, C. (2021). Performance Benchmarking and Query Optimization for Multi-Model Databases (Doctoral dissertation, University of Helsinki, Finland).
Mbaiossoum, B. L., Bellatreche, L., Batouma, N., & Daouda, A. M. (2023). Database Tuning from Relational Database to Big Data. Int. J. Eng. Trends Technol, 71(11), 90-99.
Nookala, G. (2021). Automated Data Warehouse Optimization Using Machine Learning Algorithms. Journal of Computational Innovation, 1(1).
Adnan, R., & Abbas, T. M. (2020). Materialized views quantum optimized picking for independent data marts quality. Iraqi Journal of Information and Communication Technology, 3(1), 26-39.
Davidson, L. (2020). Data Structures, Indexes, and Their Application. In Pro SQL Server Relational Database Design and Implementation: Best Practices for Scalability and Performance (pp. 773-875). Berkeley, CA: Apress.
Kavuluri, H. V. R. (2023). Query Pattern Mining for Storage Optimization in Analytical Data Engineering Platforms. High-Performance Distributed Computing Review, 10, 1-8.
Tm, H., Usman, K., & Shafiulla, M. (2023, April). An Overview of SQL Optimization Techniques for Enhanced Query Performance. In 2023 International Conference on Distributed Computing and Electrical Circuits and Electronics (ICDCECE) (pp. 1-5). IEEE.
Anuja, S., & Malathy, C. (2021). Big Data Query Optimization-Literature Survey.
Mucchetti, M. (2020). BigQuery for Data Warehousing. Springer.
Singu, S. K. (2022). Performance Tuning Techniques for Large-Scale Financial Data Warehouses.
McCarthy, S. (2021). Reusing dynamic data marts for query management in an on-demand ETL architecture (Doctoral dissertation).
Bai, Q., Alsudais, S., & Li, C. (2023). Querybooster: Improving SQL performance using middleware services for human-centered query rewriting. arXiv preprint arXiv:2305.08272.
Kodakandla, P. (2023). Refactoring petabyte-scale data warehouses for performance and cloud optimization. International Research Journal of Modernization in Engineering Technology and Science. https://doi. org/10.56726/IRJMETS34995.
Zulkifli, A. (2023). ‘Accelerating database efficiency in complex it infrastructures: Advanced techniques for optimizing performance, scalability, and data management in distributed systems. International Journal of Information and Cybersecurity, 7(12), 81-100. Aleyasen, A. (2022). Overcoming barriers in data warehouse replatforming (Doctoral dissertation, University of Illinois at Urbana-Champaign).
Sun, Y., Meehan, T., Schlussel, R., Xie, W., Basmanova, M., Erling, O., ... & Pandit, A. (2023). Presto: A decade of SQL analytics at Meta. Proceedings of the ACM on Management of Data, 1(2), 1-25.
Mohsin, M. (2023). TECHNIQUES FOR LOGICAL DESIGN AND EFFICIENT QUERYING OF DATA WAREHOUSES. JOURNAL OF ADVANCE AND FUTURE RESEARCH, 1(2), 1-10.
Hartzell, K. (2023). Comparison of Big Data SQL Engines in the Cloud.
Ajayi, J., Akindemowo, A., Erigha, E., Obuse, E., Afuwape, A., & Adebayo, A. (2023). A conceptual framework for cloud cost optimization through automated query refactoring and materialization. International Journal of Multidisciplinary Research and Growth Evaluation, 4(2), 898-914.
Ibrahim, F., & Aoun, M. (2022). Improving query efficiency in heterogeneous big data environments through advanced query processing techniques. Journal of Contemporary Healthcare Analytics, 6(6), 40-64.
Colley, D. (2021). Development of a dynamic design framework for relational database performance optimisation (Doctoral dissertation, Staffordshire University).
Ramu, V. B. (2023). Optimizing database performance: Strategies for efficient query execution and resource utilization. International Journal of Computer Trends and Technology, 71(7), 15-21.
Colley, D. (2021). Development of a dynamic design framework for relational database performance optimisation (Doctoral dissertation, Staffordshire University).
Inersjö, E. (2021). Comparing database optimisation techniques in postgresql: Indexes, query writing and the query optimiser.
Vaisman, A., & Zimányi, E. (2022). Physical Data Warehouse Design. In Data Warehouse Systems: Design and Implementation (pp. 245-295). Berlin, Heidelberg: Springer Berlin Heidelberg.
Edara, P., & Pasumansky, M. (2021). Big metadata: when metadata is big data. Proceedings of the VLDB Endowment, 14(12), 3083-3095.
Bimonte, S., Gallinucci, E., Marcel, P., & Rizzi, S. (2023). Logical design of multi-model data warehouses. Knowledge and Information Systems, 65(3), 1067-1103.
Achanta, M. Comparing Different Strategies for Handling Type 2 Slowing Changing Dimensions in Data Warehousing.
Tadi, V. (2022). Performance and Scalability in Data Warehousing: Comparing Snowflake's Cloud-Native Architecture with Traditional On-Premises Solutions Under Varying Workloads. European Journal of Advances in Engineering and Technology, 9(5), 127-139.
Mehmood, E., & Anees, T. (2020). Challenges and solutions for processing real-time big data stream: a systematic literature review. IEEE Access, 8, 119123-119143.
Li, Z., Pi, X., & Park, Y. (2023, April). S/c: Speeding up data materialization with bounded memory. In 2023 IEEE 39th international conference on data engineering (ICDE) (pp. 1981-1994). IEEE.
Downloads
Published
How to Cite
Issue
Section
License
This work is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License.
All papers should be submitted electronically. All submitted manuscripts must be original work that is not under submission at another journal or under consideration for publication in another form, such as a monograph or chapter of a book. Authors of submitted papers are obligated not to submit their paper for publication elsewhere until an editorial decision is rendered on their submission. Further, authors of accepted papers are prohibited from publishing the results in other publications that appear before the paper is published in the Journal unless they receive approval for doing so from the Editor-In-Chief.
IJISAE open access articles are licensed under a Creative Commons Attribution-ShareAlike 4.0 International License. This license lets the audience to give appropriate credit, provide a link to the license, and indicate if changes were made and if they remix, transform, or build upon the material, they must distribute contributions under the same license as the original.
