Introduction
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 more than 10,000 records by July 2020.
Currently, there are three main methods for retrieving records in bulk. This article will showcase how and when to use them.
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.
- Use Method 1: 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 Method 2: the Cursor API method when the records are sorted, and more than 10,000 records are expected to be retrieved.
- Use Method 3: the offset method when the records are sorted, and fewer than 10,000 records are retrieved.
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"
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
- For a Javascript function that retrieves record values using the seek method, refer to the Code examples for bulk record retrieval article.
- For more information on switching from Method 3: the offset method to Method 1: the seek method, refer to the Get all records from an App (the seek method) tutorial.
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:
- Create a cursor using the Add Cursor API.
- Retrieve records using the Get Cursor API.
- 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
- For a Javascript function that retrieves record values using the Cursor API method, refer to the Code examples for bulk record retrieval article.
- For more information regarding the three Cursor APIs and how to use them, refer to Bulk record retrieval using the Cursor API method tutorial.
- For a performance comparison between the Cursor API method and the offset method, refer to the Comparison section of the above-mentioned article.
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
- For a Javascript function that retrieves record values using the offset method, refer to the Code examples for bulk record retrieval article.
- For more information regarding how to retrieve an App's record using the offset method, refer to Get all records from an App (the offset method) tutorial.
SDK Support
|
SDK |
Support |
|---|---|
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.
Given the upcoming implementation of the offset parameter's upper limit of 10,000, make sure to revise programs with the optimal record retrieval methods.