Database Query Optimization via Parameter Literalization

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

Current database management systems face inefficiencies in query performance due to the generation of excessive execution plans, leading to 'plan bloat' and increased CPU usage, especially in large databases with varying parameter values, resulting in sub-optimal results and prolonged response times.

Innovation Solution

A method is introduced to alter queries by determining a second parameter name based on the parameter value, optimizing the generation and caching of execution plans, which involves modifying the query string to reuse plans for statistically determined parameters, such as limited locations or date ranges, thereby reducing the need for full database searches.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Adaptability or versatility

If the database management system generates execution plans for each query with varying parameter values, then query flexibility and adaptability are improved, but plan bloat and CPU usage increase significantly

Engineering Contradiction:

Improvequery flexibilityVSAvoidplan bloat

Core Design Contradiction:

Adaptability or versatilityVSDevice complexity

Solution Approach 1:

The patent applies parameter changes by detecting when query parameters represent a small subset of possible values (e.g., location is one of 10 cities rather than infinite possibilities) and transforming the parameterized query into multiple literal queries, each with a concrete parameter value. This transformation changes the parameter space from continuous/large to discrete/small, enabling the system to generate execution plans for each literal value while avoiding plan bloat from unlimited parameter combinations.

Inventive Principle:

Principle #35Parameter changes

Solution Approach 2:

The patent segments the single parameterized query into multiple literal queries, each targeting a specific parameter value. By dividing the query set based on parameter distinctness, the system processes each segment independently with its own execution plan, preventing the accumulation of excessive plans while maintaining adaptability for frequently accessed data subsets.

Inventive Principle:

Principle #1Segmentation

2Manufacturing precision

If the database management system creates new execution plans frequently to handle varying queries, then query optimization is improved, but response time increases due to plan generation overhead

Engineering Contradiction:

Improvequery optimizationVSAvoidresponse time

Core Design Contradiction:

Manufacturing precisionVSLoss of time

Solution Approach 1:

The patent performs preliminary action by pre-generating execution plans for all possible literal parameter values during a background process or idle period. When a query arrives with a parameter representing a small subset of values, the system has already prepared the corresponding execution plans in advance, eliminating the need for time-consuming plan generation at query execution time and thus reducing response time while maintaining optimization quality.

Inventive Principle:

Principle #10Preliminary action

3Speed

If the system stores execution plans for all parameter combinations, then query execution speed is improved, but memory consumption and plan bloat increase

Engineering Contradiction:

Improvequery execution speedVSAvoidmemory consumption

Core Design Contradiction:

SpeedVSQuantity of substance

Solution Approach 1:

The patent applies local quality by storing execution plans selectively rather than uniformly for all parameter combinations. The system identifies which parameter values are locally relevant (i.e., represent a small subset of possible values based on distinctness analysis) and generates execution plans only for those local cases. This localized plan generation reduces memory consumption while maintaining fast execution speed for the most commonly accessed data subsets.

Inventive Principle:

Principle #3Local quality

4Measurement precision

If the database system performs full database searches for parameterized queries, then query accuracy is maintained, but CPU usage and response time increase

Engineering Contradiction:

Improvequery accuracyVSAvoidresponse time

Core Design Contradiction:

Measurement precisionVSProductivity

Solution Approach 1:

The patent applies partial action by performing a full database search only when necessary (i.e., when parameters represent a large subset of possible values), and using optimized literal query execution with pre-generated plans when parameters represent a small subset. This selective approach maintains query accuracy for all cases while improving response time for the common case of frequently accessed parameter values, avoiding excessive full-scann operations.

Inventive Principle:

Principle #16Partial or excessive action

Data Source

PatentEP3436988B1"methods and systems for database optimisation"

Publication Date: 2020.12.30 WISETECH GLOBAL LTD

Figure 1~2
Figure 3~4
Figure 5a~5b

AI summary

This disclosure relates to increasing performance of database queries. A proxy server receives an input query string and a parameter value for first parameter name in the query string. The proxy server determines a second parameter name based on the parameter value and different to the first parameter name. The proxy server then determines an output query string based on the input query string. The output query string comprises a filter clause with a field name and a second field value, the second field value of the output query string being based on the second parameter name. The proxy server finally sends the output query string to a database management system to cause the database management system to execute a database query using an execution plan based on the second parameter name in the output query string.