Querying Partitioned Tables via Node Sorting and Batching
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Distributed database systems face performance bottlenecks due to the limited number of concurrent threads caused by server hardware constraints when handling large numbers of queries across multiple partitions, leading to inefficiencies in data access and processing.
Innovation Solution
A method is introduced to optimize querying partitioned tables by mapping document identifiers to their respective database nodes and partitions, sorting them, and generating statements that adhere to a maximum block size, thereby reducing communication between nodes and improving efficiency by segmenting queries into manageable packages.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If the number of concurrent queries increases to handle more data access requests, then query throughput increases, but server hardware resources (sockets and cores) become exhausted leading to performance degradation
Solution Approach 1:
The patent segments the set of document identifiers into multiple batches, where each batch is processed in a separate statement. This segmentation allows the system to handle more total queries by dividing them into manageable chunks that can be processed sequentially or in parallel without overwhelming server hardware resources.
2Loss of energy
If all document identifiers are sent in a single statement to access multiple partitions, then communication overhead is reduced, but the statement exceeds maximum block size limits preventing execution
Solution Approach 1:
The patent divides the complete set of document identifiers into multiple batches that fit within maximum block size limits. Each batch is processed in a separate statement, ensuring that no single statement exceeds the database system's block size constraints while still accessing multiple partitions efficiently.
Solution Approach 2:
The patent performs preliminary sorting of document identifiers by database node and partition before batching them. This preliminary organization ensures that each statement contains identifiers for a specific partition, optimizing the subsequent access operations and reducing the need for additional sorting or filtering at execution time.
3Productivity
If multiple statements are generated to access different partitions, then data access efficiency improves, but the number of threads required increases beyond server capacity
Solution Approach 1:
The patent segments identifiers into batches that can be processed with a limited number of concurrent threads. By controlling the batch size and number of simultaneous statements, the system maintains high data access efficiency while staying within the server's thread capacity constraints.
4Ease of manufacture
If document identifiers are not sorted by database node and partition, then query generation is simpler, but query processing time increases due to inefficient data access patterns
Solution Approach 1:
The patent performs preliminary sorting of document identifiers by database node and partition before generating access statements. This upfront organization optimizes the data access pattern, ensuring that each statement targets the correct partition efficiently, thereby reducing overall query processing time despite the additional sorting step.
Data Source
AI summary
A method and a system for querying partitioned tables in a distributed database is disclosed. In one aspect, a method includes receiving a request to access multiple data elements, identified by record identifiers from a partitioned and distributed database table. The method further includes mapping record identifiers to partitions. The method further includes sorting the record identifiers by database node and partition. The method further includes generating, for each database node and partition, at least one statement including at least one request to access a data element stored in the corresponding partition. The method further includes responding to the request by routing the at least one statement generated for each database node and partition.