Procedure Graph Operator Movement for Hybrid Query Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Optimizing database queries that combine declarative and imperative language is challenging due to the 'impedance mismatch' between the two programming paradigms, leading to inefficient query execution.
Innovation Solution
A query optimizer analyzes a procedure's graph to move operators across statements, applying optimization techniques such as pushing and pulling operators to improve performance by reducing redundant executions.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If a database system uses both declarative and imperative language constructs to handle complex query logic, then the system can achieve greater functional versatility and handle complex analytical queries, but the optimization of such procedures becomes difficult due to impedance mismatch between the two programming paradigms
Solution Approach 1:
The patent introduces an intermediary representation layer (triples representation) that mediates between declarative SQL statements and imperative procedural code. This intermediate form allows the query optimizer to handle both paradigms uniformly by converting them into a common representation that tracks variable definitions, uses, and dependencies, thereby resolving the impedance mismatch while maintaining functional versatility
Solution Approach 2:
The patent segments the complex hybrid procedure into discrete triples (variable, operation, data source) that can be independently analyzed and optimized. By breaking down the procedure into these atomic units and representing them as a directed acyclic graph, the optimizer can apply transformations to individual segments without being constrained by the mixed paradigm structure
2Productivity
If the query optimizer attempts to optimize procedures with both declarative and imperative constructs, then query execution performance can be improved, but the optimization process becomes inefficient due to the impedance mismatch between programming paradigms
Solution Approach 1:
The patent changes the representation parameters of hybrid procedures from traditional multi-paradigm code to a unified triples-based directed acyclic graph. This parameter transformation enables the optimizer to efficiently analyze and transform queries by working with a standardized data structure that explicitly models variable lifecycles and data dependencies, reducing optimization time while improving execution performance
3Adaptability or versatility
If operators are executed within loops in imperative procedures, then complex analytical queries can be performed with iterative processing, but redundant operations are executed multiple times reducing query efficiency
Solution Approach 1:
The patent applies preliminary action by identifying operators that can be extracted from loops and executed once before the iterative process begins. The optimizer analyzes the directed acyclic graph to find operators whose results are used within loops but do not depend on loop variables, and moves these operators outside the loop boundary, performing the action preliminarily to avoid redundant executions while preserving iterative processing capability
Data Source
AI summary
In some embodiments, a method receives a statement graph for a procedure that is used to access data and determines a set of producer statements and a set of consumer statements in the statement graph. A producer statement produces a variable for a consumer statement, and the consumer statement consumes the variable from the producer statement. The method optimizes the statement graph to generate an optimized procedure by moving an operator from a consumer statement to a producer statement or by moving an operator from a producer statement to a consumer statement. The optimized procedure is output based on the optimized statement graph.