Database Query Batching Across Parallel Connections
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database systems face inefficiencies in query processing due to serial transmission over a single connection, leading to delays and reduced throughput, especially in multi-tenant environments with high user demand.
Innovation Solution
Implementing a query batching system that groups queries based on database tables and expected record counts, allowing simultaneous transmission of batch queries over available connections, with result sets parsed and returned individually to maintain efficiency and reduce network bandwidth consumption.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Speed
If queries are transmitted serially over a single database connection, then data integrity and connection simplicity are maintained, but query processing speed and system throughput deteriorate
Solution Approach 1:
The patent segments the single database connection into multiple parallel connections. The query batching system creates multiple database connections that can operate simultaneously, allowing queries to be distributed across these connections rather than processed serially through a single connection. This segmentation enables parallel query execution while maintaining individual connection simplicity.
Solution Approach 2:
The patent merges multiple individual query requests into a single batch query that is transmitted over one database connection. The query batching mechanism combines multiple SELECT statements into one unified query package, which is then sent to the database server as a single unit. This merging reduces the number of connection handshakes and protocol overhead while maintaining data integrity.
2Loss of energy
If multiple queries are batched together for simultaneous transmission, then network bandwidth consumption and disk I/O demands are reduced, but query processing complexity and result set management deteriorate
Solution Approach 1:
The patent segments the batch query result set into individual result sets corresponding to each original query. After receiving the combined batch query results from the database server, the system parses and divides the result set back into separate portions, each assigned to its corresponding original query request. This segmentation simplifies result handling while maintaining the efficiency benefits of batched transmission.
Solution Approach 2:
The patent introduces a query batching intermediary layer between the application layer and the database layer. This intermediary component receives multiple individual queries, batches them together for efficient transmission, and then distributes the results back to the appropriate requestors. This mediator handles the complexity of batch processing while presenting a simple interface to both the application and database layers.
3Productivity
If a single database connection is used to serve multiple users, then connection resource utilization is improved, but user request response time deteriorates
Solution Approach 1:
The patent merges multiple user queries into batch groups that can be transmitted efficiently over the database connection. By combining multiple individual requests into unified batch queries, the system maximizes the utilization of the database connection while reducing the total time required to service all users. The batching mechanism ensures that connection resources are fully utilized without forcing users to wait for sequential processing.
Solution Approach 2:
The patent implements periodic batch transmission where queries are collected over a short interval and then transmitted together in batches. This periodic batching approach allows the system to accumulate multiple user requests and send them in organized groups, improving connection utilization while maintaining responsive service to users through the efficient batch processing cycle.
Data Source
AI summary
Systems and methods include reception of a plurality of database queries, determination of a first set of database queries from the plurality of database queries, where each of the first set of database queries requests data from a same database table, and where a total count of returned records for the first set of database queries is less than a threshold, merge of the first set of database queries into a batch query, transmission of the batch query to a database, reception of a batch query result set from the database, determination, for each of the first set of database queries, of a result set from the batch query result set, and return of the result sets for each of the first set of database queries.