Cost-Based Function Optimization for Coupled Database Systems
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Conventional database optimizers fail to account for the complexity of distributed data warehouses and coupled systems, leading to suboptimal query execution plans due to the lack of consideration for processing environments and data transfer costs across multiple systems and subsystems.
Innovation Solution
A cost-based function optimization method that identifies and evaluates processing environments for executing functions, considering costs of emulation, native implementation, hardware acceleration, and data transfer, to generate improved query execution plans that account for all coupled systems in the data warehouse.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Manufacturing precision
If conventional optimizers are used to generate query execution plans, then the query execution plan generation is simple and fast, but the query execution plan is suboptimal and does not account for processing environments and data transfer costs
Solution Approach 1:
The optimizer is segmented into multiple independent cost estimation modules, each responsible for estimating costs of specific operations (function execution, data transfer, result movement). This allows the complex optimization problem to be broken down into manageable components that can be evaluated separately and combined to produce an optimal query execution plan.
Solution Approach 2:
A cost estimation module acts as an intermediary between the query optimizer and the coupled systems (data warehouse and external systems). This intermediary evaluates multiple processing environments and data transfer scenarios, providing the optimizer with comprehensive cost information without requiring the optimizer itself to become overly complex.
2Productivity
If multiple processing environments are considered for function execution, then the cost-effectiveness of query execution is improved, but the complexity of evaluating and selecting the optimal environment increases
Solution Approach 1:
Cost estimates for function execution, data transfer, and result movement are pre-calculated and stored for each processing environment before query optimization occurs. This preliminary action allows the optimizer to quickly retrieve and compare cost information without performing time-consuming evaluations during query plan generation.
Solution Approach 2:
The system uses parameter-based cost estimation models that can be configured with system-specific characteristics (processing speeds, data transfer rates, resource availability). By changing these parameters based on current system state, the optimizer can efficiently evaluate different processing environments without re-running complex simulations.
3Measurement precision
If data transfer costs between processing environments are accounted for, then the overall execution cost accuracy is improved, but the complexity of cost calculation increases
Solution Approach 1:
The cost calculation is segmented into distinct components: function execution cost, data transfer cost, and result movement cost. Each component is calculated independently using specialized formulas and system parameters, making the overall complex calculation manageable and transparent.
Solution Approach 2:
The cost estimation module continuously monitors actual system performance and updates cost parameters accordingly. This feedback mechanism ensures that cost calculations remain accurate over time without requiring manual intervention or complex recalibration, as the system automatically adjusts to changing conditions.
Data Source
AI summary
A function reference for a function is identified in a query. A plurality of processing environments that can provide the function is identified. Function costs for the function to process in the processing environments are obtained. Input data transfer costs are acquired for providing input data identified in the query to each of the functions. A specific one of the functions from a specific processing environment is selected based on the function costs and the input data transfer costs. A query execution plan for executing the query with the specific function is generated. The query execution plan is provided to a database engine for execution.