Method and software for database performance optimization

Abstract

Modern approaches to database performance optimization are mostly focused on individual aspects – indexing, SQL query optimization, or server environment configuration which forces administrators to use multiple specialized tools simultaneously and significantly increases the likelihood of missing critical deficiencies in the database structure or configuration. The problem of comprehensive automated database analysis without direct server access remains unsolved, as most existing solutions require either a live connection to the database server or execution of profiling queries to collect runtime statistics, which makes them inapplicable in scenarios where direct access to the production system is restricted for security or technical reasons.

This paper proposes a combined algorithmic-programmatic method that integrates three analytical modules for static analysis – indexing, structural, and configuration as well as an additional interactive module for real-time SQL query refactoring. The three core modules contribute to the composite performance index (CPI), while the refactoring module operates independently as a real-time advisory tool. The method is based on static analysis of SQL dumps and server configuration files without deploying the database, which allows it to be applied in offline scenarios. Each module operates independently and targets a specific aspect of database performance: the indexing module detects missing or redundant indexes, the structural module verifies compliance with normal forms and identifies inefficient data types, the configuration module compares server parameters against recommended values, and the refactoring module provides real-time recommendations as the user writes SQL expressions.

To quantitatively evaluate optimization effectiveness, a composite performance index (CPI) is introduced, which aggregates three partial scores – indexing efficiency, structural efficiency, and configuration efficiency – using empirically determined weight coefficients of 0.40, 0.35, and 0.25 respectively. The CPI is normalized in the range from 0 to 1 and is calculated both before and after applying the recommendations, enabling objective comparison of the database state without executing any queries against the live system.

The method is implemented as a client-server web application with a React-based frontend and a FastAPI backend, supporting role-based access control with user and administrator roles. The proposed approach achieves comprehensive automated database optimization and generates actionable recommendations for the administrator without significant additional administration overhead.