Pre-calculated Association Pagination Ranking in Relational Databases
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Relational database systems face performance issues due to complex queries and high computing resource consumption during pagination in association tables, especially with large amounts of master data, leading to slower performance and inefficient CPU and memory usage.
Innovation Solution
Pre-calculated association pagination ranking is implemented by loading parent rows into memory, calculating and updating rankings for association rows during inserts, updates, or deletions, and using stored procedures to efficiently manage ranking operations, reducing runtime overhead and optimizing database queries.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If pagination is performed at the association level in relational database systems, then query results can be divided into pages for display, but computing resources are consumed unnecessarily and performance becomes slower
Solution Approach 1:
The patent applies preliminary action by pre-calculating rankings for association rows during data insertion, update, or deletion operations. The ranking information is stored in the database along with the association data. When pagination queries are executed, the pre-calculated rankings are directly used without requiring runtime calculation, thereby improving query performance and reducing computing resource consumption.
2Ease of operation
If complex queries are used for association table pagination, then complete pagination functionality is achieved, but CPU and memory usage increase leading to inefficient resource utilization
Solution Approach 1:
The patent extracts the ranking calculation operation from the pagination query execution. Instead of calculating rankings during query execution, the ranking calculation is performed separately during data modification operations (insert, update, delete). This separation allows the pagination query to simply filter and sort based on pre-calculated rankings, significantly reducing CPU and memory usage while maintaining complete pagination functionality.
Solution Approach 2:
The patent introduces an intermediary ranking column that stores pre-calculated ranking values. This intermediary structure acts as a bridge between the association data and the pagination query requirements. The ranking column enables efficient pagination by providing ready-to-use sort order information without requiring complex runtime calculations, thus reducing resource consumption while maintaining ease of operation.
3Measurement precision
If rankings are calculated at runtime during queries, then accurate pagination results are obtained, but unnecessary computing overhead is incurred
Solution Approach 1:
The patent applies preliminary action by calculating and storing ranking values in advance during data modification operations. When pagination queries are executed, the system directly retrieves and uses these pre-calculated rankings without performing additional calculation operations. This approach maintains pagination accuracy while eliminating runtime computing overhead and reducing query execution time.
Data Source
AI summary
Enhanced database query handling is provided using pre-calculated association pagination ranking. Such pre-calculated rankings can be generated using various approaches including by calculating rank along with an intersection, addition, and deletion of association rows corresponding to a parent row being modified or by a stored procedure which uses the ranking operations. Related apparatus, systems, techniques and articles are also described.