Database Query Optimization via Automatic Scenario Recognition

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

Current database query optimization methods are inefficient and time-consuming, requiring manual tuning and expertise, especially when data volumes change, and are not effective in handling complex SQL queries or varying database properties.

Innovation Solution

A computer-implemented framework that automatically recognizes optimization scenarios in database queries and rewrites them using pre-defined operations, generating optimized query plans tailored to the specific characteristics of the underlying database, reducing the need for manual tuning and expertise.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Reliability

If manual query tuning is performed, then query optimization can be achieved, but the process is extremely time-consuming and difficult

Engineering Contradiction:

Improvequery optimization effectivenessVSAvoidtuning time

Core Design Contradiction:

ReliabilityVSLoss of time

Solution Approach 1:

The system enables self-service optimization by automatically analyzing query plans and generating optimized queries without requiring user intervention. The query optimizer independently evaluates execution plans, identifies optimization opportunities, and generates rewritten queries that improve performance, eliminating the need for manual tuning while maintaining optimization effectiveness

Inventive Principle:

Principle #25Self-service

Solution Approach 2:

The system performs preliminary optimization actions by pre-defining optimization scenarios and operations before query execution. The framework analyzes query syntax to recognize optimization scenarios in advance and automatically applies pre-defined operations to generate optimized queries, avoiding the need for time-consuming manual analysis during execution

Inventive Principle:

Principle #10Preliminary action

2Reliability

If manual performance tuning is performed, then query performance can be improved, but it requires extensive knowledge of the specific database properties

Engineering Contradiction:

Improvequery performanceVSAvoidoperational complexity

Core Design Contradiction:

ReliabilityVSEase of operation

Solution Approach 1:

The system eliminates the need for user expertise by automatically analyzing query syntax to recognize optimization scenarios and applying appropriate optimizations based on predefined operations. The framework independently determines the best optimization strategy without requiring users to understand database-specific properties, making query optimization accessible to all users regardless of their database knowledge

Inventive Principle:

Principle #25Self-service

Solution Approach 2:

The system changes operational parameters by automatically adjusting query execution parameters based on recognized optimization scenarios. When the framework identifies specific query patterns, it transforms query parameters according to predefined optimization rules tailored to different database types, achieving performance improvement without requiring users to manually adjust parameters or understand database-specific optimization techniques

Inventive Principle:

Principle #35Parameter changes

3Reliability

If the entire tuning process is repeated when data volume changes, then optimization can be maintained, but it is tedious and time-consuming

Engineering Contradiction:

Improveoptimization effectivenessVSAvoidtuning efficiency

Core Design Contradiction:

ReliabilityVSProductivity

Solution Approach 1:

The system performs preliminary optimization setup by pre-defining multiple optimization scenarios and operations that can be automatically applied to different query types. When data volume changes, the framework reuses these pre-defined optimization rules to quickly adapt queries without requiring complete retuning, significantly improving productivity while maintaining optimization effectiveness

Inventive Principle:

Principle #10Preliminary action

Solution Approach 2:

The system provides dynamic optimization by automatically detecting data volume changes and adjusting query execution plans in real-time. The framework monitors data characteristics and dynamically selects appropriate optimization scenarios, allowing queries to adapt to changing data volumes without requiring manual retuning, thus maintaining productivity and optimization effectiveness

Inventive Principle:

Principle #15Dynamics

4Loss of information

If EXPLAIN PLAN function is used, then query plan can be previewed, but it returns many unknown results making it difficult to judge efficiency

Engineering Contradiction:

Improvequery plan informationVSAvoidexecution time measurement

Core Design Contradiction:

Loss of informationVSMeasurement precision

Solution Approach 1:

The system implements feedback mechanisms by actually executing optimized queries to obtain real execution time data, which is then fed back to the user. Instead of providing speculative plan information with many unknowns, the framework delivers concrete performance measurements from executed queries, enabling users to accurately assess query efficiency and make informed decisions about optimization

Inventive Principle:

Principle #23Feedback

Data Source

PatentUS9009140B2Optimization of database query

Publication Date: 2015.04.14 SAP SE

AI summary

Described herein is a technology for facilitating optimization of database queries is described herein. In some implementations, a first query is received. At least one scenario for optimization is automatically recognized by detecting specific syntax in the query. A second query is then automatically generated by re-writing the first query using a pre-defined operation corresponding to the recognized scenario.