Unified Query Optimization via Hoist and Sink Operations
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current optimization strategies for database query processing are inadequate in managing the increasing demand on computing resources, particularly in scenarios involving both query statements and imperative constructs like loops, where existing techniques fail to fully optimize resource utilization.
Innovation Solution
A novel optimization strategy that involves receiving an intermediate representation of a procedure containing query and imperative statements, performing hoist and sink operations to relocate query statements within the procedure, and estimating computing resource demands to determine an optimal execution plan that minimizes resource usage.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If traditional query optimization strategies are used, then query execution can be optimized using indexes and JOIN ordering, but the increasing demand on computing resources cannot be fully managed
Solution Approach 1:
The patent applies dynamics by making the query execution plan adaptive through iterative optimization. The system dynamically adjusts query statements based on execution feedback and resource consumption patterns, transforming static optimization into a dynamic process that responds to actual computing resource demands and performance characteristics.
Solution Approach 2:
The patent changes key parameters of query execution including statement placement, iteration counts, and execution ordering. By systematically varying these parameters across multiple execution plans and selecting the optimal one, the system achieves better resource utilization while maintaining query efficiency.
2Device complexity
If query statements are executed outside loops, then query optimization is simpler, but query inlining combines multiple queries increasing execution plan complexity
Solution Approach 1:
The patent merges multiple query statements into a single inlined query when executing within loops. This combining approach reduces the total number of separate query executions, thereby decreasing computing execution resource demand while the optimization system manages the resulting execution plan complexity through systematic search and selection methods.
3Use of energy by moving object
If more query statements are inlined, then execution resource demand decreases, but the optimization process becomes more complex
Solution Approach 1:
The patent performs preliminary actions by systematically enumerating multiple execution plan candidates before final optimization. The system pre-generates various query placement configurations and their associated resource demands, then selects the optimal plan. This preliminary enumeration approach manages optimization complexity through structured search rather than ad-hoc decision making.
Data Source
AI summary
Optimization of procedures for enterprise applications can take both declarative query statements and imperative logic into account in a unified optimization technique. An input procedure can implement complex analytical queries and also include iterative control flow logic such as loops. Alternative query execution plans for the procedure can be enumerated by moving queries out of and into loop boundaries via hoist and sink operations. Program correctness can be preserved via excluding some operations via dependency graphs. Sink subgraphs can also be used. Query inlining can also be supported, resulting in synergies that produce superior execution plans. The computing execution resource demand of the respective alternatives can be considered to arrive at an optimal query execution plan that can then be used to actually implement execution of the procedure. Execution performance can thus be greatly improved by performing counterintuitive optimizations.