The three methods for bulk record retrieval

 

Introduction

The Get Records API offset parameter has an upper limit of 10000. This article introduces when and how to use three methods for retrieving records in bulk, with the 10000 limit in consideration.

Basic Concepts of the Three Methods

The three methods to retrieve records in bulk are the seek method, the Cursor API method, and the offset method.

Flowchart differentiating the 3 methods.
The above flowchart differentiates the three methods.

Method 1: the seek method

This method uses the Get Records API to sort and retrieve the records by the Record ID (in ascending order).
This method is commonly known as the "Seek method" for Relational Database Management System (RDBMS). The article, Paging Through Results, explains why the "Seek method" performs at high speed.

 

Two points to keep in mind when using the Record IDs:

  • Use the following filter: "Record ID > Record ID of the last record retried in the prior session"
  • Re-sort by the Record ID with "order by $ id asc"

Graphic indicating that a 500 record selection has Record IDs 1 to 500 and the next section has Record IDs 501 to 1000.
The above image visualizes how the seek method process through 500 records at a time.

Suitable Scenarios

  • When the records are not sorted by any particular criteria. (i.e., records are sorted by the Record ID).
  • When it is possible to sort the record within the program after retrieving the records.

Reasons

  • With this method, the time to retrieve records is linearly proportional to the number of records. Therefore, records can be retrieved at high speeds.
  • However, a complicated query is needed to get records sorted by multiple fields.
    The code example presented in the Code examples for bulk record retrieval article is configured to handle sorting by multiple fields.

For More Information

Method 2: the Cursor API method

This method uses the Cursor APIs to retrieve records.
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 specific record's locations. The cursors allow for retrieving records based on their position information.

 

The steps for retrieving records in bulk using the three Kintone Cursor APIs are as follows:

  1. Create a cursor using the Add Cursor API.
  2. Retrieve records using the Get Cursor API.
  3. Once no longer needed, delete the cursor using the Delete Cursor API.

Suitable Scenarios

  • When the required number of cursors can be estimated, such as batch processing.

Reasons

  • Compared with using an offset in Method 1, the time required to retrieve records is consistent regardless of the sort conditions and the number of records.
  • The number of cursors that can be simultaneously created per domain is limited to 10.
    Therefore, this method is suitable for processes where the required number of cursors can be estimated or controlled.
  • This method is not well-suited for Kintone JavaScript customization where simultaneous requests from multiple users can be expected.

For More Information

Method 3: the offset method

This method uses the Get records API to retrieve records sequentially by specifying the offset as a request parameter.
By executing the Get records API with a specified "offset" and "limit", the "limit" number of records from "offset" onward can be retrieved.

  • The offset request parameter indicates the number of rows to skip before retrieving the first record.
  • The limit request parameter indicates the maximum number of records to retrieve.

To get records sequentially using this method, gradually increase the offset value to specify the desired record's location.

Suitable Scenarios

  • When the number of records to be retrieved does not exceed 10,000.
  • When a limitation can be set to only retrieve up to 10,000 records.

Reasons

  • Implementing this method is simple since records can be obtained sequentially simply by specifying the offset and limit.

For More Information

SDK Support

SDK

Support

Kintone iOS SDK

Method 3: the offset method

Finally

This article introduced the three methods for bulk record retrieval: the seek method, the Cursor API method, and the offset method.
The methods vary in performance, complexity, and limitations, thus understanding their differences is essential when creating Kintone plug-ins and customizations.

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!