Time-Dependent Graph Index for Hierarchical Database Queries
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Database protocols, such as ABAP Open SQL, are inefficient in handling parent-child relationships and time-dependent hierarchies, leading to poor performance due to the lack of appropriate commands for building powerful recursive queries, which results in multiple individual queries and increased resource consumption.
Innovation Solution
A graph index is generated for the database that specifies direct and indirect successor nodes along with their validity time windows, allowing for efficient retrieval of time-dependent hierarchical information using a single SQL command, thereby reducing the burden on application servers and improving query response time.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Loss of information
If multiple individual queries are executed to retrieve hierarchical information, then complete hierarchical data can be obtained, but query response time increases and resource consumption increases
Solution Approach 1:
The patent pre-calculates and stores the transitive closure (all ancestors and descendants) for each node in the hierarchy during database initialization or maintenance operations. This preliminary computation allows the system to answer hierarchical queries instantly by simply retrieving pre-computed results, eliminating the need for multiple runtime queries and significantly reducing query response time while maintaining complete hierarchical information.
2Loss of information
If multiple individual queries are executed to retrieve hierarchical information, then complete hierarchical data can be obtained, but processor and memory consumption increases
Solution Approach 1:
The system performs the computationally intensive transitive closure calculation in advance during database setup or maintenance windows, rather than executing multiple queries at runtime. The results are stored in a dedicated hierarchy table that can be efficiently queried. This shifts the computational burden from runtime query processing to preliminary data preparation, significantly reducing processor and memory consumption during actual query operations while maintaining complete hierarchical data availability.
3Adaptability or versatility
If hierarchical calculations are performed in memory at runtime by the application server, then flexible queries can be executed, but application server load increases and performance decreases
Solution Approach 1:
The patent extracts the computationally intensive hierarchical calculation logic from the application server and implements it directly within the database system. The database maintains a specialized hierarchy table containing pre-computed ancestor and descendant relationships. This extraction allows the application server to simply issue straightforward SQL queries without bearing the computational load of complex hierarchical traversals, thereby maintaining query flexibility while significantly improving application server performance and reducing its resource utilization.
4Reliability
If time-dependent relationships are handled using conventional database protocols, then temporal hierarchy changes can be tracked, but query complexity and execution time increase
Solution Approach 1:
The system pre-computes hierarchical relationships with time validity ranges, storing each node's ancestors and descendants along with their valid time periods in the hierarchy table. When querying, the system simply retrieves pre-computed results filtered by the requested time range, rather than executing complex recursive queries that traverse the entire hierarchy. This approach maintains accurate tracking of temporal hierarchy changes while dramatically simplifying query execution and reducing complexity.
Data Source
AI summary
Data is received that characterizes a data structure comprising a plurality of hierarchical nodes in which dependencies amongst a portion of the nodes are time-dependent. Based on the received data, a graph index is generated for a database that specifies, for each node, all corresponding direct and indirect successor nodes and associated validity time windows. Subsequently, the database receives queries that specify, in a single database protocol command, a request for time-dependent hierarchical information of a node. The database then provides results responsive to the queries (if any). Related apparatus, systems, techniques and articles are also described.