Database Batch Job Assignment via Real-Time Cost Analysis
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Database systems face performance issues due to inefficient access paths, especially in complex relational databases, where periodic statistics may not reflect real-time data changes and volatile tables lack relevant statistics, leading to resource-intensive operations and delays.
Innovation Solution
A computer-implemented method using a machine learning module to identify real-time statistics and select efficient access paths based on dynamic database changes, calculating costs for batch jobs across multiple buffer pools to optimize resource allocation and reduce delays.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Device complexity
If the database system uses periodic statistics to determine access paths, then the system can operate with simpler mechanisms, but the access paths may not reflect real-time data changes leading to inefficient data retrieval
Solution Approach 1:
The patent implements dynamic access path determination by continuously monitoring real-time statistics such as row counts, distinct values, and data distribution patterns. The system adapts access paths based on current database state rather than relying on periodic snapshots, ensuring optimal data retrieval efficiency while maintaining manageable system complexity through automated monitoring and adaptation mechanisms.
2Reliability
If the database system searches for many variables matched to many different records on separate tables, then comprehensive query results can be obtained, but resource consumption increases and performance decreases
Solution Approach 1:
The patent applies preliminary action by pre-calculating and storing statistics about data distribution, row counts, and relationship patterns between tables. When queries are executed, the system uses these pre-computed statistics to quickly determine optimal access paths without performing exhaustive searches, thereby reducing resource consumption while maintaining complete and accurate query results.
3Productivity
If the database system uses parallel jobs to increase servicing speed, then throughput improves, but coordination overhead and complexity increase
Solution Approach 1:
The patent segments the database system into multiple independent members, each with its own buffer pool and ability to handle batch jobs autonomously. The optimization module calculates costs for executing jobs on different members and assigns jobs to appropriate members based on real-time conditions. This segmentation allows parallel processing to improve throughput while reducing coordination overhead, as each member operates independently with minimal inter-dependency.
Data Source
AI summary
In a computer-implemented method for improving performance of a database, a processor receives batch jobs for a relational database. The batch jobs may include a first member with a first buffer pool, and a second member with a second buffer pool. The processor may also identify a first actual object and an isolation level for the batch jobs, generate related queries based on the first actual object and the isolation level, calculate a cost for the first member and the second member to run the batch jobs based on the related queries, and assign the batch jobs to the first member based on a lower calculated cost.