Get All Records from an App: The Offset Method vs The Cursor API Method
Overview
This article introduces how to retrieve data of all records from an App. The Offset Method and the Cursor API Method are two approaches to retrieving all records. This article introduces these methods, and compares their performance.
The Cursor API Method
API Overview
There are 3 REST APIs in Kintone related to cursors.
In computer science, a database "cursor" is an object that points to the record currently being processed.
Similar to how a typing cursor shows where the user's words will appear next, a database cursor also shows the record that is being worked on at that moment. Cursors are created in a database and used to keep track of a specific record's location. The cursors allow for retrieving records based on their position information.
How to Retrieve Records
Create a cursor using the Add Cursor API
A Database Cursor is created using the
Add Cursor API
. The query
parameter defines the cursor's range.
Retrieve records using the Get Cursor API
Multiple records are retrieved by specifying the Cursor ID with the Get Cursor API .
The Get Cursor API is used, and the cursor position shifts by the size
parameter specified in the Add Cursor API request. The records that were passed over by the cursor are then retrieved.
Repeat the Get Cursor API
The Get Cursor API is repeatedly used against the same cursor until all queried records are retrieved from the App.
Sample Code
Navigate to an App, and open the browser's developer tools. Type the following code into the console and run it.
|
|
Data of all records inside the App should be output into the console.
Performance of the Two Methods
Comparison overview
This section compares the performance of the Offset Method method and the Cursor API Method. The following graphs illustrate the time the two methods took retrieving records under various conditions. The time it took to create the cursor is also included.
Case 1: 100,000 records retrieved sorted by Record ID
The Cursor API method and the Offset Method take almost the same time to retrieve the records. The time increases linearly as the records to retrieve increase.
Case 2: 100,000 records retrieved sorted by Text field
The Cursor API Method takes a slightly longer time to retrieve records compared to Case 1. In comparison, the Offset Method notably takes more time. The slope for the Cursor API Method is very similar to Case 1, while the slope for the Offset Method is nearly 45-degrees.
Case 3: 500,000 records retrieved sorted by Record ID
Both the Cursor API and Offset Methods have a nearly 45-degree slope. The Cursor API Method is slightly faster at retrieving records.
Case 4: 500,000 records retrieved sorted by Text field
The difference in time between the Cursor API and Offset Methods grows greatly as the records to retrieve approaches 500,000. The slope for the Cursor API Method is relatively flat, while the slope for the Offset Method is nearly 45-degrees.
Conclusion
The graphs show that the Cursor API Method is consistently faster at retrieving records than the Offset Method. This difference in speed between the methods increases as more records are retrieved. The Offset Method's performance is inconsistent, with varied retrieval times depending on the sort method and the number of records.
Limitations
- The upper limit for the
offset
parameter of the Get Records API is 10,000. - Only a maximum of 10 Cursors can be created at once per domain.
- The Add Cursor API times out after 5 minutes.
- Cursors expire 10 minutes after the last request made with the Add Cursor API or the Get Cursor API .
- A maximum of 500 records can be retrieved per Get Cursor API request.
- Refer to the API documentation for additional restrictions: Add Cursor API , Get Cursor API , Delete Cursor API .