Query Resource Optimizer for Dynamic SQL Allocation
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing approaches to running SQL queries in parallel within a database face challenges such as resource overloading and delays, as they fail to dynamically allocate resources effectively, leading to poor performance.
Innovation Solution
A computer-implemented method that involves training a model to identify SQL queries, grouping incoming queries based on shared programs, monitoring resource usage, and dynamically allocating resources to optimize query execution.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Speed
If SQL queries are run in parallel using first come first get approach, then query execution speed is improved, but resource allocation becomes inefficient and lower priority queries occupy resources
Solution Approach 1:
The patent implements dynamic resource allocation by continuously monitoring query priorities and resource usage in real-time. The system dynamically adjusts resource distribution based on current query characteristics and system state, allowing higher priority queries to receive more resources when needed while ensuring fair allocation to lower priority queries, thus resolving the contradiction between execution speed and resource efficiency
Solution Approach 2:
The system employs feedback mechanisms where resource usage metrics and query priorities are continuously monitored and fed back into the allocation decision-making process. This feedback loop enables the system to learn from past allocations and optimize future resource distribution, preventing resource hoarding by lower priority queries while maintaining fast execution for high priority queries
2Quantity of substance
If SQL queries are run in parallel using low priority low resource approach, then resource consumption is reduced, but query execution performance deteriorates due to delays
Solution Approach 1:
The patent applies local quality by differentiating resource allocation based on individual query characteristics and priorities. Instead of uniform resource distribution, the system allocates resources locally to each query based on its specific needs and priority level, allowing high priority queries to receive adequate resources for fast execution while lower priority queries receive proportional resources, thus avoiding both resource exhaustion and execution delays
3Productivity
If more SQL queries are executed in parallel, then throughput is improved, but computing resources become exhausted
Solution Approach 1:
The system dynamically changes allocation parameters such as resource quotas, priority weights, and execution thresholds based on system state and query characteristics. By adjusting these parameters in real-time, the system can maximize throughput by allowing more parallel queries when resources are abundant while preventing resource exhaustion by reducing parallelism when resources are constrained, thus resolving the contradiction between throughput and resource consumption
Data Source
AI summary
A method includes training, by a computing device, a model to identify SQL queries; identifying, by the computing device, incoming SQL queries; grouping, by the computing device, the incoming SQL queries into groups; monitoring, by the computing device, resource usage of the incoming SQL queries in the groups; and allocating, by the computing device, resources to an SQL query of the incoming SQL queries based on the monitoring.