Imperative Code Optimization in Relational Database Engines
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current database systems inefficiently evaluate imperative code, such as User Defined Functions (UDFs), due to iterative execution, lack of costing, limited predicate pushdown, interpreted execution, and parallelism limitations, leading to poor performance.
Innovation Solution
Converting imperative code into declarative forms using standardized relational operators, allowing query optimizers to optimize UDFs by transforming multi-statement UDFs into single relational algebra expressions, applying simplification rules, and incorporating compiler optimizations.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If imperative code is executed using traditional iterative evaluation methods, then code modularity and reuse are maintained, but execution performance is poor due to lack of optimization
Solution Approach 1:
The patent introduces an intermediary translation layer that converts imperative code into declarative SQL representations. This intermediary process enables the query optimizer to work with standardized relational operators, achieving efficient optimization without requiring changes to the original imperative code structure or losing code modularity.
Solution Approach 2:
The patent replaces the mechanical iterative evaluation system with a declarative query system that uses standardized relational operators. This substitution allows the query optimizer to apply sophisticated optimization techniques such as cost-based optimization, predicate pushdown, and parallel execution, dramatically improving performance.
2Measurement precision
If fixed costs are assigned to imperative functions for query optimization, then optimization can proceed, but accuracy is poor because actual costs change over time with technology changes
Solution Approach 1:
The patent transforms the cost estimation approach by changing the parameters from fixed static values to dynamic values derived from actual SQL operator costs. By translating imperative functions into declarative SQL representations, the system can use current, accurate cost information that reflects actual hardware and system conditions, eliminating the need for outdated fixed cost assignments.
3Productivity
If imperative code is converted to declarative query representations, then query optimization efficiency is improved, but conversion complexity increases
Solution Approach 1:
The patent creates a universal translation framework that handles multiple imperative code patterns (loops, conditionals, function calls) using a unified declarative SQL representation approach. This universal method simplifies the conversion process by providing consistent rules and patterns for translating various imperative constructs into optimized SQL, reducing the apparent complexity despite the powerful optimization capabilities enabled.
Data Source
AI summary
Processing a database query. A method includes receiving a database query from a user. The database query includes one or more imperative functions. The one or more imperative functions are converted to one or more declarative query representations. The one or more declarative query representations include standardized relational operators included in a relational query language. Further, the one or more declarative query representations are optimizable by a query optimizer of the database. The database query is optimized at the query optimizer to create a query plan by evaluating any declarative query representation originally in the database query received from the user and the one or more declarative query representations.