Join the program

Bulk record retrieval using the Cursor API method

Last Updated:
Follow

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.

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:

  1. 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.

     

  2. 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

     

  3. Repeat Step 2 until all desired records are retrieved.
    *Keep retrieving the records by running additional GET request to the same cursor.

     

    Step 2 is repeated to retrieve all records within the cursor's scope

     

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

Graph comparing the Cursor API and offset methods shows both methods takes virtually the same time in retrieving records.

 

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

Graph comparing the Cursor API and offset methods in retrieving records shows that Cursor API takes far less time than the offset method.

 

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

Graph comparing the Cursor API and offset methods shows both methods takes very similar time in retrieving records.

 

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

Graph comparing the Cursor API and offset methods in retrieving records shows that Cursor API takes far less time than the offset method.

 

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.

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
Was this article helpful?
0 out of 0 found this helpful
Do you have any questions or issues related to this article?
Please share your views with us in the Community forums!