SQL Query Timeout Control for Performance Regression Testing

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

Managing performance regressions in database management systems (DBMS) is challenging due to the complexity of systems, numerous queries, and the need for scalable, automated solutions to detect and handle regressions efficiently and reliably, especially in large-scale, multi-tenant cloud environments.

Innovation Solution

An automatic performance regression management (Auto-PRM) framework that includes a query processing engine with a cache manager, query optimizer, and an automatic performance regression manager to detect and mitigate performance regressions by using execution history, hint manager, and knowledge sharing across tenants.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Reliability

If manual management of query performance regressions is performed, then deep understanding and control of query processing can be achieved, but scalability and efficiency deteriorate in large-scale systems with numerous queries

Engineering Contradiction:

Improveperformance regression detection accuracyVSAvoidscalability

Core Design Contradiction:

ReliabilityVSProductivity

Solution Approach 1:

The system implements automated performance regression management where the database management system itself detects, analyzes, and resolves performance regressions without external intervention. The query optimizer automatically compares execution plans against performance baselines, identifies regressions, and applies corrections, enabling the system to self-manage its performance while maintaining deep understanding of query processing internals.

Inventive Principle:

Principle #25Self-service

Solution Approach 2:

The system establishes continuous feedback loops by monitoring query execution performance, comparing actual performance against baseline metrics, and automatically adjusting query plans when regressions are detected. This closed-loop feedback mechanism enables scalable automated management while maintaining reliable performance detection through systematic comparison and analysis of execution statistics.

Inventive Principle:

Principle #23Feedback

2Productivity

If automated performance regression management is implemented, then scalability and efficiency are improved, but system complexity increases due to additional monitoring and management mechanisms

Engineering Contradiction:

ImprovescalabilityVSAvoidsystem complexity

Core Design Contradiction:

ProductivityVSDevice complexity

Solution Approach 1:

The performance regression management system leverages existing multi-functional components within the database management system. The query optimizer serves both its traditional function of generating execution plans and the additional function of detecting performance regressions. The execution engine simultaneously performs query processing and collects performance metrics, reducing overall system complexity by avoiding dedicated separate monitoring infrastructure.

Inventive Principle:

Principle #6Universality (Multi-functionality)

Solution Approach 2:

The system merges performance monitoring and regression detection functions into the existing query processing pipeline. By combining these functions with the query optimizer and execution engine, the system achieves scalable automated management without adding significant complexity overhead, as the monitoring and management mechanisms are integrated into already-present system components.

Inventive Principle:

Principle #5Merging (Combining)

3Speed

If query plans are cached to improve efficiency, then query execution speed is improved, but performance regressions may go undetected and persist

Engineering Contradiction:

Improvequery execution speedVSAvoidperformance regression detection

Core Design Contradiction:

SpeedVSReliability

Solution Approach 1:

The system implements feedback mechanisms that continuously monitor query execution performance even when using cached query plans. By comparing actual execution performance against established baselines and detecting deviations, the system can identify performance regressions in cached plans and trigger automatic reoptimization, thus maintaining both execution speed through caching and reliable regression detection through continuous monitoring.

Inventive Principle:

Principle #23Feedback

Solution Approach 2:

The system performs preliminary performance validation before caching query plans by comparing them against performance baselines. This preliminary action ensures that only performant query plans are cached, preventing poor-performing plans from being stored and executed, thereby maintaining both execution speed and regression detection capability.

Inventive Principle:

Principle #10Preliminary action

Data Source

PatentUS20250390496A1SQL execution timeout for automatic query performance regression management

Publication Date: 2025.12.25 SAP SE

AI summary

A computer implemented method can detect performance regression of executing a query using a current query plan. Responsive to detecting the performance regression, the method can generate an alternative query plan as a candidate solution for resolving the performance regression, start a test execution session in a designated execution thread of a query processing engine to execute the query using the alternative query plan, and monitor an elapsed duration after starting the test execution session in a timer thread of the query processing engine. The timer thread is independent of the designated execution thread. Responsive to detecting that the elapsed duration exceeds a timeout value and the test execution session has not been completed, the method can terminate the test execution session in the designated execution thread. Related systems and software for implementing the method are also disclosed.