Integrating Calculation Models into SQL Execution Plans
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Conventional database systems lack integration of calculation models with the execution model of SQL optimizers, leading to suboptimal query execution times and limited optimization possibilities, as calculation models are not compatible with SQL execution formats.
Innovation Solution
A database system that integrates calculation models into execution plans by converting them into a format compatible with SQL engines during query compilation, allowing the SQL optimizer to incorporate and optimize the combined execution plan, thereby improving execution efficiency and enabling further optimizations.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If calculation models are executed separately after SQL query execution, then complex data flows and calculations can be performed, but query execution time increases and optimization possibilities are limited
Solution Approach 1:
The patent merges the SQL execution model and calculation model execution into a single integrated execution plan. The SQL engine parses the query and identifies calculation views, then invokes the calculation engine during query compilation to convert calculation models into SQL-compatible format. Both execution models are combined into one unified execution plan that is optimized and executed together, eliminating separate execution phases and enabling comprehensive optimization across both relational and non-relational operations.
Solution Approach 2:
The calculation model is converted into SQL-compatible format during query compilation before execution. The calculation engine instantiates the calculation model, converts it to a format compatible with the SQL engine, and integrates it into the execution plan during compilation time. This preliminary conversion enables the SQL optimizer to apply optimizations to the combined execution plan before execution, rather than waiting until execution time.
2Ease of operation
If calculation models are not integrated with SQL execution model, then each engine can operate independently with its own optimizer, but the combined execution plan cannot be optimized by the SQL optimizer
Solution Approach 1:
The calculation engine serves multiple functions: it executes calculation models independently when invoked directly, and also converts calculation models into SQL-compatible format for integration with the SQL execution model. This multi-functionality allows the system to maintain independence of engines while enabling integration when beneficial for optimization.
Solution Approach 2:
The calculation engine acts as an intermediary between the calculation model and the SQL execution model. It converts the calculation model into a format compatible with the SQL engine, enabling the SQL optimizer to optimize the combined execution plan. This intermediary conversion process allows both engines to operate independently while still enabling comprehensive optimization when integrated.
3Productivity
If calculation models are converted to SQL-compatible format during query compilation, then optimizations can be applied at compilation time, but the conversion process adds complexity to the query processing
Solution Approach 1:
The calculation engine performs self-service by automatically converting calculation models into SQL-compatible format and integrating them into the execution plan. This automated conversion process reduces the need for manual intervention and simplifies the overall query processing, despite the added conversion step. The system handles the complexity internally while presenting a unified interface to the user.
Data Source
AI summary
According to an aspect, a database system for integrating calculation models into execution plans includes a first engine configured to parse a query to be applied on a database. The first engine is configured to invoke a second engine during query compilation. The second engine is configured to instantiate a calculation model based on the query, and the second engine is configured to derive a converted calculation model by converting the calculation model into a format compatible with the first engine. The first engine is configured to incorporate the converted calculation model into an execution plan during the query compilation and execute the query on the database according to execution plan.