Query Optimization Engine Coordination for Suboptimal Execution Prediction

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

In complex computing environments, such as cloud or large-scale distributed systems, existing query optimization methods fail to efficiently utilize resources across multiple query optimization engines, leading to suboptimal query execution and system performance issues.

Innovation Solution

A computer-implemented method and system that monitors query execution across multiple databases and optimization engines, retains statistical information, predicts suboptimal execution, and alters the execution plan to optimize queries by reallocating them to available engines with suitable capacity or type, enabling pre-emptive optimization and resource management.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Ease of operation

If query optimization is performed using traditional load balancing methods based on basic metrics like server idleness, then the system can maintain simple operation and basic resource distribution, but query execution efficiency deteriorates and resource utilization becomes suboptimal in complex computing environments

Engineering Contradiction:

Improveoperation simplicityVSAvoidquery execution efficiency

Core Design Contradiction:

Ease of operationVSProductivity

Solution Approach 1:

The system changes the parameters used for load balancing from basic metrics like server idleness to multiple factors including engine capacity, engine type matching, and statistical information about query execution patterns. This allows the system to optimize query execution by selecting the most suitable engine based on comprehensive parameters rather than simple metrics.

Inventive Principle:

Principle #35Parameter changes

Solution Approach 2:

The system replaces traditional mechanical load balancing mechanisms with an intelligent prediction system that uses statistical information and machine learning models to predict suboptimal execution scenarios. This substitution enables proactive query redistribution before performance degradation occurs, rather than relying on reactive basic metrics.

Inventive Principle:

Principle #28Mechanics substitution (Replace mechanical system)

2Quantity of substance

If multiple query optimization engines are deployed across distributed systems, then resource utilization capacity increases, but the complexity of managing and coordinating these engines increases

Engineering Contradiction:

Improvenumber of optimization enginesVSAvoidsystem management complexity

Core Design Contradiction:

Quantity of substanceVSDevice complexity

Solution Approach 1:

The system introduces a centralized coordinator that acts as an intermediary between multiple query optimization engines. This coordinator collects statistical information from all engines, makes intelligent decisions about query distribution, and manages engine coordination. This intermediary simplifies the complexity of managing multiple engines by centralizing control logic.

Inventive Principle:

Principle #24Intermediary (Mediator)

Solution Approach 2:

The system implements feedback mechanisms where statistical information about query execution performance is continuously collected and used to inform future query routing decisions. This feedback loop enables the system to learn from past performance and dynamically adjust query distribution to optimize overall system efficiency while managing engine complexity.

Inventive Principle:

Principle #23Feedback

3Measurement precision

If query execution is monitored and statistically analyzed in real-time, then prediction accuracy for suboptimal execution improves, but the computational overhead and time consumption increase

Engineering Contradiction:

Improveprediction accuracyVSAvoidtime for monitoring and analysis

Core Design Contradiction:

Measurement precisionVSLoss of time

Solution Approach 1:

The system performs preliminary actions by pre-collecting and storing statistical information about query execution patterns before actual queries need to be optimized. This advance preparation of statistical data enables faster real-time predictions without incurring significant computational overhead during query execution, as the heavy lifting of data collection and analysis is done beforehand.

Inventive Principle:

Principle #10Preliminary action

Data Source

PatentUS12511290B1Query optimization and distribution

Publication Date: 2025.12.30 INTERNATIONAL BUSINESS MACHINE CORPORATION

AI summary

Computer implemented methods, systems, and computer program products include program code executing on a processor(s) monitors query execution within the database system within the multiple databases and by the multiple query optimization engines. The program code retains statistical information from the monitoring in a centralized database. The program code obtains, at a query optimization engine of the database system, a new query. The program code predicts, based on accessing the statistical information in the centralized database, that executing the new query with the query optimization engine at a designated execution time comprises a suboptimal execution of the new query. The program code alters a planned execution of the new query at the designated execution time with the query optimization engine to optimize execution of the query within the database system.