Introduction
As mentioned in the API Updates for July 2019 post, the following three new Kintone APIs were implemented in July 2019 collectively called the Cursor APIs:
This article describes the Cursor APIs and illustrates how to retrieve records in bulk using the APIs with a sample code.
About the Cursor APIs
In July 2020, the Get Records API's offset parameter will have an upper limit of 10,000. Make sure to revise programs that retrieve records in bulk that may return over 10,000 records by then.
The Cursor APIs were added to optimize the rapid retrieval of a large number of records from an App. Even with more complex sort conditions, the Cursor API method offers quick response times. Refer to The Cursor API and the Offset Methods Comparison section for more information.
There are three main methods for retrieving records in bulk:
- Method 1: the seek method - use when the records are not sorted by any particular criteria. (i.e., the records are sorted just by the Record ID).
- Method 2: the Cursor API method - use when the records are sorted, and more than 10,000 records are expected to be retrieved.
- Method 3: the offset method - use when the records are sorted, and fewer than 10,000 records are retrieved.
For more information about the differences between the three methods and their suitable scenarios, refer to The three methods for bulk record retrieval article.
The rest of this article will go in depth on Method 2: the Cursor API method.
What is a Database Cursor?
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 Using the Cursor APIs?
The sequence for retrieving records using these APIs is as shown below:
-
Create a cursor using the Add Cursor API
*The record search query defines the cursor's range.![User sends a [ POST /k/v1/records/cursor.json ] to Kintone which leads to a cursor generated with a defined range.](/hc/article_attachments/360000773321/cursor_1.png)
-
Retrieve records using the Get Cursor API
*The cursor moves by the "size", specified in the Add Cursor (POST) request, per Get Cursor (GET) request. The records that were passed over by the cursor can be retrieved.![User sends a [ GET /k/v1/records/cursor.json ] to Kintone which leads to Kintone sending back Records that were passed over by the cursor](/hc/article_attachments/360000773281/cursor_2.png)
-
Repeat Step 2 until all desired records are retrieved.
*Keep retrieving the records by running additional GET request to the same cursor.
Sample Code
The sample code below retrieves records in bulk using the Cursor APIs
The Cursor API and the Offset Methods Comparison
This section will compare the performance of the Cursor API method and the offset method. The following graphs illustrate the time the two methods took retrieving the records under various conditions. The time it took to create the cursor is included. The sorting conditions used to query for the records were configured on line 12 of the sample code provided above.
Case 1
- Retrieved 100,000 records
- Sorted by $id (i.e., Record ID)
Graph
The Cursor API method and the offset method took virtually the same time to retrieve the records. The time gradually increased linearly as the records to retrieve increased.
Case 2
- Retrieved 100,000 records
- Sorted by Text field
Graph
The Cursor API method takes a slightly longer time to retrieve the records now compared to Case 1. In comparison, the offset method takes dramatically 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
- Retrieved 500,000 records
- Sorted by $id (i.e., Record ID)
Graph
Both the Cursor API and offset methods have a nearly 45-degree slope, although the Cursor API method is slightly faster.
Case 4
- Retrieved 500,000 records
- Sorted by Text field
Graph
The difference in time between the Cursor API and offset methods grows dramatically 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 results in the graphs speak for themselves: 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. Also, the offset method's performance is inconsistent, with varied retrieval times depending on the sort method and the number of records.
Finally
An upper limit of 10,000 will be implemented for the Get Records API's offset parameter. The Cursor APIs were added to optimize the bulk record retrieval; thus, make sure to revise programs that retrieve more than 10,000 records by July 2020.
- For an overview of the three methods to retrieve records in bulk, refer to The three methods for bulk record retrieval article.
- For code examples using the three methods, refer to the Code examples for bulk record retrieval article.
- For more information on another alternative to the offset method, refer to the Get all records from an App (the seek method) article.
Restrictions
- Only a maximum of 10 Cursors can be created at once per domain.
- The Add Cursor API will timeout 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