Remote Database Query Optimization via Capability Assessment
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing data retrieval methods from remote databases face performance issues due to sub-optimal query execution, leading to excessive or incorrect data retrieval, especially with complex queries and varying remote database capabilities.
Innovation Solution
A computer-implemented method that determines the capabilities of remote databases through an iterative process, using create view commands to assess and adjust the query execution plan, ensuring that only executable commands are pushed down to the remote servers, thereby optimizing query performance and reducing resource waste.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If complex queries are pushed down to remote databases using traditional rule-based methods, then query execution may fail due to incompatible remote database capabilities, but avoiding pushdown increases bandwidth usage and reduces query efficiency
Solution Approach 1:
The system performs capability assessment before executing the main query by sending create view commands to test if the remote database can handle specific query operations. This preliminary action determines which parts of the query can be safely pushed down, preventing execution failures while optimizing query efficiency.
Solution Approach 2:
The system uses the results from capability assessment commands to dynamically adjust the query execution plan. Based on feedback about remote database capabilities, the system determines whether to push down query operations or execute them locally, creating a closed-loop control system that adapts to remote database capabilities.
2Adaptability or versatility
If traditional rule-based pushdownability analysis is used, then the system can determine which commands to execute remotely, but the rules become obsolete with hardware/software changes requiring frequent updates
Solution Approach 1:
The system automatically assesses remote database capabilities by executing create view commands and interpreting the results. This self-service approach eliminates the need for manual rule updates when hardware or software changes occur at remote databases, as the system dynamically discovers capabilities through testing.
Solution Approach 2:
The system changes from using static, pre-defined rules to dynamically determining query pushdown capabilities based on actual remote database responses. The capability parameters are updated based on real-time assessment results rather than relying on obsolete rule sets.
3Reliability
If all query data is retrieved and processed locally, then query execution reliability is maintained, but bandwidth usage increases and remote processing capabilities are underutilized
Solution Approach 1:
The system segments the query into different parts based on remote database capabilities. Operations that the remote database can handle are pushed down and executed remotely, while other operations are executed locally. This segmentation allows selective use of remote processing capabilities without compromising overall query reliability.
Data Source
AI summary
Disclosed is a computer implemented method and related system to improve the efficiency of querying remote databases. The method includes receiving, from a host, a query, wherein the query is configured to retrieve a set of data from a remote database. The method also includes, generating an access plan, the access plan comprising a plurality of nodes wherein each node of the plurality of nodes includes a command. The method further includes determining capabilities of the remote database. The method includes executing the query and returning the set of data to the host.