Mixed Query Plan Operator Selection for Database Performance

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

Database management systems face challenges in optimizing query execution plans due to the complexity and volume of queries, leading to performance issues in handling transactions and multidimensional analysis.

Innovation Solution

A method is introduced that generates a mixed query plan incorporating pre-compiled and code-generating operators, with operator alternatives allowing for delayed selection based on additional information. This approach includes inserting glue code to facilitate execution between different types of operators.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Adaptability or versatility

If a database management system handles complex multidimensional analysis queries, then the system can perform sophisticated data analysis, but the query execution performance deteriorates due to the complexity and volume of queries

Engineering Contradiction:

Improvecapability to handle complex multidimensional analysisVSAvoidquery execution performance

Core Design Contradiction:

Adaptability or versatilityVSProductivity

Solution Approach 1:

The query execution plan is segmented into multiple operators, each handling specific aspects of query processing. The system divides the complex query into manageable operator components that can be independently optimized and executed, improving overall query performance while maintaining complex analysis capabilities

Inventive Principle:

Principle #1Segmentation

Solution Approach 2:

The system dynamically changes execution parameters by selecting between pre-compiled and code-generated operators based on query characteristics. This parameter adaptation allows the system to optimize performance for different types of complex queries while maintaining versatility in handling various analysis scenarios

Inventive Principle:

Principle #35Parameter changes

2Speed

If the system uses pre-compiled operators for query execution, then execution speed is improved, but the adaptability to handle diverse query types deteriorates

Engineering Contradiction:

Improvequery execution speedVSAvoidability to handle diverse query types

Core Design Contradiction:

SpeedVSAdaptability or versatility

Solution Approach 1:

The system dynamically selects between pre-compiled and code-generated operators based on the specific query characteristics and execution context. This dynamic adaptation allows the system to maintain high execution speed for suitable queries while preserving adaptability for diverse query types that require custom code generation

Inventive Principle:

Principle #15Dynamics

Solution Approach 2:

The query optimization system serves multiple functions by supporting both pre-compiled operators for speed and code-generated operators for adaptability. This multi-functional approach allows a single system to handle both performance-critical and diverse query scenarios effectively

Inventive Principle:

Principle #6Universality (Multi-functionality)

3Adaptability or versatility

If the system generates code at runtime for operator execution, then adaptability to query variations is improved, but execution time increases due to code generation overhead

Engineering Contradiction:

Improveadaptability to query variationsVSAvoidcode generation overhead time

Core Design Contradiction:

Adaptability or versatilityVSLoss of time

Solution Approach 1:

The system performs preliminary actions by pre-compiling operators that are likely to be used frequently. This advance preparation reduces runtime code generation overhead for common query patterns while maintaining adaptability for variations through selective code generation only when necessary

Inventive Principle:

Principle #10Preliminary action

4Productivity

If the system optimizes query execution plans with multiple operator alternatives, then query performance is improved, but the complexity of the query plan generation process increases

Engineering Contradiction:

Improvequery execution efficiencyVSAvoidquery plan generation complexity

Core Design Contradiction:

ProductivityVSDevice complexity

Solution Approach 1:

The system applies partial optimization by selecting operator alternatives based on query characteristics without exhaustively evaluating all possible combinations. This partial action approach improves query performance through selective optimization while avoiding the excessive complexity of complete plan exploration

Inventive Principle:

Principle #16Partial or excessive action

Data Source

PatentUS12204539B2Automatic selection of precompiled or code-generated operator variants

Publication Date: 2025.01.21 SAP SE

AI summary

A system for generating a mixed query plan including operator alternatives. A method may include generating a mixed query plan including a first operator selected as a pre-compiled operator; generating the mixed query plan including a second operator selected as operator alternatives, the operator alternatives configuring the second operator as pre-compiled or code-generating alternatives; delaying selection of one of the operator alternatives until additional information regarding the mixed query plan becomes available; generating the mixed query plan including a third operator selected as a code-generating operator; and selecting, given the third operator representing the additional information, one of the operator alternatives to enable execution of the mixed query plan using the selected operator alternative. Related methods and articles of manufacture are also described.