Database Query Analysis and Optimization in a Large Scale Information System

Abstract

Information Systems play an integral role in several aspects of businesses and society. They provide valuable insights by collecting and analyzing data and help optimal decision makings. With the advancement in technologies, the scale of information systems also increases. Modern requirements of Information systems demand high scalability to support very large-scale needs. Large-scale essentially means the data volume is large and data access frequency is very high. Resolving bottlenecks and avoid- ing common pitfalls in Information systems is the key to achieving higher scalability. DHIS2 is a web application originally designed for collecting, aggregating, and analyzing statistical health data. DHIS2 is used in more than 73 different countries, each with its implementation and use cases. Due to the covid pandemic, the demand for a scalable DHIS2 system increased and Covid contact tracing and Covid vaccination tracking. Even though DHIS2 is used mainly in the Health domain, there are also implementations of DHIS2 in other sectors like Education. Some of these implementations need to support a country-wide scale. Such large-scale DHIS2 implementations frequently suffer from performance issues and bottlenecks. This thesis aims to study the types of performance issues faced by large-scale Information Systems. I focus on various large-scale DHIS2 implementations and investigate the bottlenecks both on the application side and database side of DHIS2. The thesis also aims at finding out optimization techniques and changes to improve performance and clear these bottlenecks. The results of this research are generalized in such a way that they can be applied to any Information system and not just DHIS2. The results show successful optimization changes and how much of an impact these changes have had on the performance of real-world large-scale DHIS2 implementations. Qualitative analysis of the performance improvement is done to understand the impact of each optimization.