The Three Methods for Bulk Record Retrieval

Contents

Overview

This article introduces three methods for bulk retrieval of records and when to use them. Different scenarios require different methods to minimize performance issues.

How to Choose Which Method to Use

The three methods to retrieve records in bulk are the Seek Method, the Cursor API Method, and the Offset Method.

  • Use The Seek Method when the records are not sorted by any particular criteria. (i.e., the records are sorted just by the Record ID).
  • Use The Cursor API Method when the records are sorted, and more than 10,000 records are expected to be retrieved.
  • Use The Offset Method when the records are sorted, and fewer than 10,000 records are expected to be retrieved.

Refer to the following flowchart for choosing which method to use.

Flowchart: The user is guided to which method they should be using depending on their scenario.

For information on how to code each method, refer to the following article:

The Seek Method

Method overview

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

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

Refer to the following image to visualize how the records are retrieved.

Image: The first 500 selection of records have Record IDs 1 to 500, and the next selection of records have Record IDs 501 to 1000.

Suitable Scenarios

  • When the records do not need to be 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

  • The time to retrieve records is linearly proportional to the number of records. Therefore, large number of records can be retrieved without needing to worry about the retrieval time growing exponentially.

Reference

For more information on the Seek Method and its performance, refer to the following articles:

The Cursor API method

Method overview

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.
  • This method is not well-suited for Kintone JavaScript customization where simultaneous requests from multiple users can be expected.

Reasons

  • Compared with using the Offset Method, 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 at a given time can be estimated or controlled.

Reference

For more information on the Cursor API Method and its performance, refer to the following article:

The Offset Method

Method overview

This method uses the Get Records API to retrieve records sequentially by specifying the offset`in the request parameter.

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

To get records sequentially using this method, gradually increase the offset value to specify the desired record’s location. Note that the maximum value that can be set for the offset parameter is 10,000.

Suitable Scenarios

  • When the number of records to be retrieved does not exceed 10,000.

Reasons

  • Implementing this method is simple since records can be obtained sequentially simply by specifying the offset and limit.
  • The maximum value that can be set for the offset parameter is 10,000.

Reference

For more information on the Offset Method, refer to the following article: