METHODS OF OPTIMISING QUERIES IN DISTRIBUTED DATABASES

Abstract

The article provides a comprehensive study of the problem of optimizing query performance in distributed database systems, where architectural complexity, information fragmentation, and the necessity for coordination between numerous nodes significantly affect processing speed and stability. It has been determined that conventional approaches to executing multi-table queries, especially when working with large historical data collections, do not provide acceptable response times and result in significant computational costs. In this context, it is relevant to implement specialized optimization methods that reduce the number of reading operations, the volume of intermediate calculations, and the load on the network infrastructure. The article proposes an integrated approach that combines caching, indexing, and materialization of representations to achieve the maximum performance when processing complicated analytical queries in a distributed environment. The caching provides rapid retrieval of results without accessing the disk structures, the indexing speeds up the search and filtering by creating the specialized data structures, and the materialization eliminates the need to repeat complex join and aggregation operations by storing the results in the form of physically accessible tables. For the experimental part of the research, a representative multi-table database was used, which contains information about the company's employees, their salaries, and their affiliation with departments and divisions. Five different variants of the same query were executed: without optimization, after caching, after indexing, using materialized representation, and using a combination of all three methods. The experimental results demonstrate that although each method separately provides a noticeable acceleration of performance, the greatest effect is achieved when they are used simultaneously. The query execution time was decreased from 2831.869 ms to 0.076 ms, which is an increase in performance by more than 37,000 times. The data obtained confirm the existence of a synergistic effect and demonstrate the high practical importance of comprehensive optimization for the systems that work with heavy analytical loads in distributed data processing conditions. The presented approach can be used to modernize existing information systems and increase their efficiency when scaling.