Database Query Regression Detection With Execution Plan Feedback
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current database management systems do not monitor or check for query regression, leading to slower response times that may not be noticed until they impact customer interactions.
Innovation Solution
A system and method to determine regression of a database query by monitoring metrics, comparing performance measures, and generating a regression report to identify and address inefficient execution plans.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If the database system generates an execution plan based on database statistics and query constraints, then the query can be executed, but the execution plan may not be optimal leading to slower response times and query regression
Solution Approach 1:
The patent implements a feedback mechanism by continuously monitoring query performance metrics (execution time, I/O operations, CPU usage) and comparing them against baseline performance. When regression is detected through this feedback loop, the system generates alerts and can trigger automatic execution plan reoptimization, resolving the contradiction between initial execution plan generation and subsequent performance degradation.
Solution Approach 2:
The system performs preliminary actions by establishing performance baselines before regression occurs and proactively monitoring for degradation. By setting up performance thresholds and detection mechanisms in advance, the system can identify and address query regression before it significantly impacts productivity, thus preventing rather than just reacting to performance issues.
2Device complexity
If the database system does not monitor for query regression, then system complexity is reduced, but regression may not be noticed until it impacts customer interactions
Solution Approach 1:
The monitoring system is designed to be self-service by automatically collecting performance metrics, comparing them against baselines, detecting regression conditions, and generating alerts without requiring external intervention. The system serves itself by autonomously maintaining performance monitoring capabilities, thus adding minimal complexity while significantly improving reliability through continuous self-assessment.
3Measurement precision
If performance measures are stored and compared for all database queries, then query regression can be detected, but data storage and processing requirements increase
Solution Approach 1:
The system applies local quality by selectively monitoring and storing performance measures based on query characteristics, execution frequency, and business criticality. Rather than uniformly monitoring all queries, the system focuses measurement resources on high-priority queries where regression detection provides the most value, thus achieving adequate measurement precision for critical operations while minimizing overall data storage requirements.
Data Source
AI summary
A method for determining regression of a database query associated with a first execution plan includes receiving a first performance measure for the database query and the first execution plan; determining a presence of at least one stored performance measure for the database query in a data repository; adding the first performance measure and identifiers for the database query and the first execution plan to a synchronization list; retrieving a second performance measure associated with a second execution plan from the data repository; determining a regression of the database query by comparing the first performance measure to the second performance measure; in response to a determination that the database query is regressed, adding the first performance measure, the second performance measure, and identifiers for the database query and the first execution plan to a regression report.