Explicit Key Paging Database Record Retrieval
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current database systems require multiple transactions to retrieve data responsive to SQL queries, which can be inefficient in terms of network bandwidth and processing power, especially when dealing with large datasets and paginated record retrieval.
Innovation Solution
A method is introduced that generates and executes optimized SQL statements to identify and retrieve specific pages of records by using a second query that determines the difference between subsets of records, minimizing the number of requests to the database server and reducing data storage needs on client and server devices.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If traditional database systems retrieve data using multiple transactions to get all responsive records first and then filter them on the client side, then the client device can obtain complete data sets, but network bandwidth is wasted and processing power is consumed inefficiently
Solution Approach 1:
The patent applies preliminary action by having the server perform pagination calculations and identify specific page records before transmitting data. The server executes SQL queries with LIMIT and OFFSET clauses to pre-determine which records belong to the requested page, filtering data before network transmission. This prevents unnecessary data from being sent over the network, addressing the contradiction by improving retrieval efficiency while reducing network bandwidth consumption.
Solution Approach 2:
The patent extracts only the necessary subset of records corresponding to the specific page requested by the client. Instead of retrieving all responsive records and then filtering them, the system extracts precisely the records needed for the current page using server-side pagination logic. This extraction approach improves productivity by reducing the amount of data processed and transmitted, while simultaneously reducing network bandwidth loss.
2Reliability
If the database system materializes intermediate results in their entirety, then complete data sets are available for processing, but substantial processing power and memory are consumed
Solution Approach 1:
The patent applies partial action by retrieving only the portion of data needed for the specific page rather than materializing all intermediate results. The server executes queries that limit results to the requested page boundaries using OFFSET and LIMIT clauses, avoiding the excessive processing and memory consumption associated with materializing complete data sets. This maintains reliability for the specific pagination operation while significantly reducing processing power consumption.
3Ease of operation
If client devices store complete data sets locally for offline access and pagination, then data availability is improved, but data storage requirements on client devices increase significantly
Solution Approach 1:
The patent applies preliminary action by having the server perform all pagination and filtering operations before data transmission. The server calculates the exact range of records needed for the requested page and retrieves only those records. This eliminates the need for client devices to store complete data sets, as clients receive only the specific page data they need, thereby reducing client storage requirements while maintaining data accessibility for the current view.
4Ease of operation
If multiple transactions are used between client device and database system to retrieve data in pages, then precise page control is achieved, but the number of requests to the database server increases
Solution Approach 1:
The patent merges the pagination logic and data retrieval into a single optimized server-side operation. Instead of using multiple separate transactions to fetch different portions of data, the system combines the pagination parameters (page number, records per page) with a single SQL query that uses OFFSET and LIMIT to retrieve the entire requested page in one transaction. This maintains precise page navigation control while reducing the number of database requests and associated time loss.
Data Source
AI summary
A method of retrieving a page of records from a database system may include receiving a first query from a client device that, when processed by a database system, identifies a plurality of records that have a specified order and that are transmittable to the client device in pages that each have a fixed number of records. The method may further include receiving input identifying a specific page of records; generating a second query from the first query and the received input; submitting the second query to the database system for processing to retrieve the determined records; and transmitting the retrieved, determined records to the client device. When processed, the second query may (i) identify a first subset of the plurality of records; (ii) identify a second subset; and (iii) determine records in the first subset that are not in the second subset.