Virtual Database Query Optimization via Real-Time Statistics
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Conventional virtual database systems face performance degradation due to delayed responses and higher processing costs, especially in real-time queries, as they lack real-time system and data source statistics updates, leading to inefficient access plan decisions across heterogeneous and geographically dispersed database management systems.
Innovation Solution
A method that collects real-time system and data source statistics across disparate remote platforms, updates a global catalog, and optimizes query access plans based on these statistics to improve response time and processing efficiency, utilizing a statistics pushdown analysis process and system statistics collection agent to facilitate data source object management and pushdown operations.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Loss of time
If conventional virtual database systems use delayed statistics updates, then system complexity is reduced, but query response time deteriorates
Solution Approach 1:
The system performs preliminary actions by collecting and updating statistics before queries are executed. The statistics collection agents continuously gather data about data source changes, and the global catalog is updated in advance with current statistics, enabling the query optimizer to make informed decisions without real-time computation overhead.
Solution Approach 2:
Statistics collection agents act as intermediaries between the heterogeneous data sources and the global catalog. These agents collect statistics from various data sources and push them to the global catalog, which then serves as an intermediary providing optimized statistics to the query optimizer, decoupling the complexity of data source monitoring from query processing.
2Productivity
If real-time statistics collection is implemented across all data sources, then access plan optimization improves, but processing cost increases
Solution Approach 1:
The system applies local quality by implementing statistics collection selectively at each data source location based on its characteristics and importance. Not all data sources require the same level of monitoring intensity - the system can adjust collection frequency and depth locally at each data source while maintaining global optimization capability.
Solution Approach 2:
The system performs partial action by collecting statistics only for the specific data sources and query patterns that require optimization. Rather than continuously monitoring all possible parameters across all data sources, the system collects only the necessary statistics for current query optimization needs, reducing unnecessary processing overhead.
3Measurement precision
If the global catalog is updated with frequent statistics, then query optimization accuracy improves, but data transfer volume increases
Solution Approach 1:
The system extracts only the essential and changed statistics from the data sources rather than transferring complete data sets. The statistics collection agents identify and extract only those statistical parameters that have changed or are relevant to current query patterns, minimizing data transfer while maintaining optimization accuracy.
Solution Approach 2:
The global catalog is updated periodically based on change detection rather than continuously. The statistics collection agents monitor for changes in data sources and trigger updates only when necessary, reducing the frequency of data transfers while ensuring the catalog remains current enough for effective query optimization.
Data Source
AI summary
Methods, computer program products, and systems are presented. The methods include, for instance: collecting real time system statistics on a system environment in a remote platform running a remote data source amongst a plurality of remote data sources of a virtual database. Real time data source statistics on one or more data source objects from the remote data source of the virtual database is also gathered. A global catalog of the virtual database is updated with the real time system statistics and the real time data source statistics. An access plan to process a query to the virtual database is optimized based on the real time system statistics and the real time data source statistics and a response is produced based on the access plan, with improved performance and reduced cost.