Query Plan Generator for Near Real-Time Decision Support
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Decision support systems face challenges in providing timely and relevant information due to large amounts of distributed data across heterogeneous systems, where some information becomes outdated quickly, making it difficult to identify and obtain necessary data within the required time frame for effective decision-making.
Innovation Solution
A computer system that includes a query handler and query plan generator to determine information values by calculating computational and synchronization latencies, optimizing query plans across remote and replica databases to maximize information value, using a formula IV=QV(1−λCL)CL(1−λSL)SL, and managing workloads through genetic algorithms to sequence queries for optimal execution.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Loss of information
If data is distributed across multiple remote databases and replica databases, then information availability and data coverage are improved, but query execution time and synchronization latency increase
Solution Approach 1:
The system segments data across multiple remote databases and replica databases, allowing queries to be distributed across different data sources. The query optimizer divides the overall query into sub-queries that can be executed in parallel on different database segments, reducing total execution time while maintaining comprehensive information availability.
Solution Approach 2:
The system performs preliminary actions by pre-synchronizing data to replica databases before queries are executed. This allows query execution to use pre-prepared data copies, reducing the time needed to access information while maintaining data availability across the distributed system.
2Speed
If data is replicated across multiple databases at different synchronization times, then data access speed is improved, but data freshness and consistency deteriorate
Solution Approach 1:
The system dynamically adjusts query execution strategies based on the synchronization status of replica databases. The query optimizer evaluates the freshness of data in different replicas and dynamically selects the most appropriate data sources, balancing access speed with data consistency by choosing replicas that meet the query's freshness requirements.
Solution Approach 2:
The system changes parameters by adjusting the synchronization timing and data freshness thresholds based on query requirements. Different queries can specify different acceptable latency levels, allowing the system to use older replicas for non-critical queries while ensuring fresh data for time-sensitive operations, thus balancing speed and consistency.
3Loss of information
If all available information is provided to decision makers, then completeness of information is improved, but information processing complexity and time consumption increase
Solution Approach 1:
The system extracts only the relevant information needed for decision-making from the distributed databases, rather than providing all available data. The query optimizer identifies and extracts specific data elements that directly address the decision-maker's needs, reducing processing complexity while maintaining information completeness for the decision context.
Solution Approach 2:
The system applies partial action by providing a curated subset of information that is sufficient for decision-making without presenting the entire dataset. Rather than overwhelming decision-makers with excessive data, the system delivers the necessary portion of information that achieves the decision support goal with minimal processing complexity.
4Reliability
If query execution waits for future synchronization times, then data freshness is improved, but query response time deteriorates
Solution Approach 1:
The system dynamically determines whether to wait for future synchronization based on query requirements and current replica status. For queries requiring high data freshness, the system may wait for synchronization; for less time-critical queries, it uses available replicas immediately, dynamically balancing freshness against response time based on real-time conditions.
Solution Approach 2:
The system changes the synchronization wait parameter based on query characteristics. Different queries can specify different acceptable freshness levels and response time constraints, allowing the optimizer to adjust whether to wait for synchronization or use current replica data, thus adapting the freshness-time tradeoff to each specific query's needs.
Data Source
AI summary
Information-based decision support may be provided by a query handler configured to receive a query which is applicable against different combinations of a plurality of remote databases and a corresponding plurality of replica databases including at least some replicated data of respective ones of the remote databases, wherein each replica database is synchronized with a corresponding remote database at a plurality of synchronization times and the different combinations include future versions of the replica databases defined by corresponding synchronization times. A query plan generator may be configured to determine information values associated with at least a subset of the different combinations, based on a query value associated with the query and on a diminishment of the query value caused by a corresponding combination, and further configured to generate, based on the information values, a query plan including at least one combination of the different combinations for executing the query therewith.