Database Filter Pushdown with Dynamic Join Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Complex calculations in database systems often result in numerous queries that consume significant resources, leading to inefficient data transport and processing due to the lack of effective filter optimization during dynamic joins.
Innovation Solution
The database server optimizes calculation scenarios by pushing down filter attributes to the lowest available child node and removing non-required join-attributes, thereby reducing unnecessary intermediate results and enhancing query processing efficiency.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If filters are applied at higher levels of the calculation scenario, then query processing flexibility is maintained, but unnecessary intermediate results are generated consuming significant resources
Solution Approach 1:
The patent applies preliminary action by pushing filters down to the lowest possible level in the calculation scenario before execution. This allows filtering to occur as early as possible in the data flow, preventing unnecessary intermediate results from being generated in the first place, rather than applying filters later at higher levels
2Adaptability or versatility
If dynamic joins are employed to maintain flexibility in calculation scenarios, then query adaptability is improved, but filter optimization becomes difficult leading to increased resource consumption
Solution Approach 1:
The patent segments the calculation scenario into hierarchical levels and applies filter pushdown selectively to each level. By dividing the calculation graph into manageable segments with identifiable lowest levels, the system can optimize filters at appropriate granularities while preserving the overall dynamic join structure and query flexibility
3Reliability
If all attributes from calculation scenarios are included in query results, then completeness of data is ensured, but unnecessary data transport occurs between application server and database server
Solution Approach 1:
The patent extracts and removes non-required join attributes from the calculation scenario during optimization. By identifying and eliminating attributes that are not needed for the specific query while preserving required attributes, the system reduces data transport overhead without compromising the completeness of necessary results
Data Source
AI summary
A query that requests a filter attribute is received by a database server from a remote application server that is associated with a calculation scenario that defines a data flow model including one or more calculation nodes. Subsequently, the database server instantiates the calculation scenario. As part of the instantiation, the calculation scenario is optimized by (i) pushing down a filter attribute from a first node to a lowest available child node, (ii) removing the filter attribute from the first node, and (iii) removing non-required join-attributes from the instantiated calculation scenarios. Thereafter, the operations defined by the calculation nodes of the instantiated calculation scenario can be executed to result in a responsive data set. Next, the data set is provided to the application server by the database server.