Hybrid Query Execution Plan for Database Logic Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current database systems face inefficiencies in executing custom business logic, leading to increased data transfer between applications and databases, which can be time-consuming and limit the full utilization of dataset potential.
Innovation Solution
A hybrid data flow execution plan is generated by translating domain-specific query languages (DSQL) into a combination of declarative and procedural operators, using a cost model to optimize the execution plan and reduce data transfer costs by processing logic directly within the database layer.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If all business logic is expressed on application side, then users can implement custom functions flexibly, but large amounts of data are transferred between application and database which is time consuming
Solution Approach 1:
The patent extracts data-intensive processing logic from the application layer and relocates it to the database layer through stored procedures and domain-specific query languages. This allows custom business logic to be implemented within the database engine, eliminating the need to transfer large volumes of data between application and database, thereby reducing data transfer time while maintaining implementation flexibility.
Solution Approach 2:
The patent introduces domain-specific query languages (DSQL) and stored procedures as intermediary layers between the application and the underlying database. These intermediaries enable users to express custom business logic in a domain-specific manner while the database engine executes the logic efficiently, avoiding unnecessary data transfer between application and database layers.
2Ease of operation
If domain-specific query languages are used, then users can express custom business logic easily, but execution efficiency may be reduced compared to standard SQL
Solution Approach 1:
The patent creates a hybrid execution plan that generates multiple execution strategies for the same domain-specific query. It produces both a declarative execution plan (optimized for efficiency) and a procedural execution plan (faithful to the original DSQL logic). The system then evaluates both plans and selects the more efficient one, thus maintaining ease of operation while ensuring optimal execution efficiency.
Solution Approach 2:
The patent transforms the execution parameters by converting domain-specific query language statements into equivalent standard SQL operations where possible. The hybrid execution plan rewrites DSQL queries into optimized SQL forms that leverage the database engine's native capabilities, thereby improving execution efficiency while preserving the original query's business logic semantics.
3Loss of information
If complex queries with multiple joins are executed, then comprehensive data can be retrieved, but execution time increases significantly
Solution Approach 1:
The patent applies preliminary actions by pre-compiling and optimizing complex query plans at compile time. The query optimizer analyzes the query structure, determines the most efficient execution strategy, and prepares optimized execution plans in advance. This preliminary optimization reduces the execution time of complex queries with multiple joins while ensuring complete data retrieval, as the optimizer can apply sophisticated strategies like join reordering, predicate pushdown, and index selection before execution begins.
Data Source
AI summary
A procedural pattern in a received query execution plan can be matched to a stored pattern for which an equivalent declarative operator has been pre-defined. The query execution plan can describe a query for accessing data. A hybrid execution plan can be generated by replacing the procedural pattern with the equivalent declarative operator. A hybrid execution plan processing cost can be assigned to execution of the hybrid execution plan and a query execution plan processing cost can be assigned to execution of the query execution plan. The assigning can include evaluating a cost model for the hybrid execution plan and the query execution plan. The query can be executed using the hybrid execution plan if the hybrid execution plan processing cost is less than the query execution plan processing cost or the query execution plan if the hybrid execution plan processing cost is greater than the query execution plan processing cost. Related systems, methods, and articles of manufacture are disclosed.