Database Query Batching for Faster Single-Connection Processing
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
The inefficiency in transmitting and responding to database queries over a single database connection, particularly in Software-as-a-Service models, leads to delays and bottlenecks, affecting user experience and system throughput.
Innovation Solution
Implementing a query batching system that groups multiple queries based on database tables and expected record counts, allowing simultaneous transmission over a database connection, with result sets parsed and returned individually for each query.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Speed
If queries are sent serially over a single database connection, then the database connection can be kept simple and manageable, but the query processing speed and system throughput are reduced
Solution Approach 1:
The patent segments queries into batches grouped by database table, allowing multiple queries to be transmitted simultaneously over the database connection. This segmentation enables parallel processing of queries while maintaining manageable connection structure, directly resolving the contradiction between query processing speed and connection management complexity.
Solution Approach 2:
The system performs preliminary grouping of queries by database table before transmission. By pre-organizing queries into batches based on their target tables, the system enables efficient simultaneous transmission without increasing connection complexity, as the grouping logic is executed before the actual database communication occurs.
2Productivity
If multiple queries are transmitted simultaneously over a database connection, then query processing speed increases, but network bandwidth consumption and disk I/O increase
Solution Approach 1:
The patent merges multiple queries into batches that are transmitted simultaneously over the database connection. By combining queries that access the same database table into single batch requests, the system increases throughput while minimizing the number of separate network transmissions and disk I/O operations, thus resolving the contradiction between productivity and energy consumption.
3Loss of time
If queries are processed sequentially, then the database connection remains simple and stable, but the time to respond to user requests increases
Solution Approach 1:
The system dynamically groups queries into batches based on their target database tables while maintaining stable connection management. The batching mechanism adapts to different query patterns and table access scenarios, enabling faster response times through parallel processing without compromising connection stability, thus resolving the contradiction between response time and connection reliability.
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.