Database SQL Optimization via Performance Snapshotting
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Manual selection of SQL sentences for optimization in database systems is inefficient, as it fails to accurately identify sentences in need of optimization, leading to suboptimal response speed improvements.
Innovation Solution
A method and system that snapshot performance data of SQL sentences, calculate variance in execution frequencies and buffer data volumes, and select sentences for optimization based on total and single-execution buffer data volumes, ensuring high-frequency and high-performance SQL sentences are targeted.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If manual selection of SQL sentences is used for optimization, then the optimization process can be performed, but the accuracy of identifying SQL sentences in need of optimization is low and optimization efficiency is poor
Solution Approach 1:
The system enables SQL sentences to automatically identify their own optimization needs through self-monitoring of execution performance metrics. The SQL sentences are evaluated based on their own execution frequency, buffer gets, and response time data, eliminating the need for manual DBA intervention in selecting which sentences to optimize.
Solution Approach 2:
The system implements continuous feedback loops by monitoring SQL execution performance metrics (buffer gets, executions, response time) and using this feedback to automatically identify and prioritize SQL sentences that require optimization. The feedback mechanism compares current performance against thresholds and historical data to dynamically adjust optimization priorities.
2Reliability
If all SQL sentences are optimized, then comprehensive performance improvement may be achieved, but the optimization cost and time consumption increase significantly
Solution Approach 1:
The system applies optimization resources selectively to specific SQL sentences that exhibit poor performance characteristics, rather than uniformly optimizing all SQL sentences. By identifying and targeting only the problematic sentences based on performance metrics, the system achieves local optimization where it is most needed, reducing overall optimization time and resources.
Solution Approach 2:
The system performs partial optimization by focusing on a subset of SQL sentences that meet specific performance criteria (high buffer gets, high execution frequency, poor response time). This partial action approach concentrates optimization efforts on the most impactful sentences rather than attempting to optimize everything, thereby reducing time consumption while maintaining effectiveness.
3Reliability
If manual DBA selection is used for SQL optimization, then expert judgment can be applied, but the scalability and automation level of the optimization process is low
Solution Approach 1:
The system replaces the mechanical manual process of DBA selection with an automated computational system that uses performance metrics, algorithms, and data analysis to identify SQL sentences requiring optimization. The automated system substitutes human manual intervention with machine-based decision-making, significantly increasing automation level while maintaining optimization quality through objective metric-based evaluation.
Data Source
AI summary
The disclosure relates to a method of optimizing a database system, including: snapshotting performance data of each Structured Query Language sentence of the database system every preset first time, and arranging and storing snapshots according to a time sequence, wherein the performance data include the number of times of executions and a buffer gets data volume; correspondingly carrying out variance calculation on the performance data of the current snapshot and the performance data of the former snapshot respectively to calculate a total buffer gets data volume and a single-execution buffer gets data average volume which correspond to each SQL sentence within the preset first time; obtaining a value sum of the total buffer gets data volume and the single-execution buffer gets data average volume of each SQL sentence, and selecting an SQL sentence to be optimized on the basis of the value sum.