Backing up records of a Kintone App or synchronizing them with other systems normally requires writing a code to run the Get Records REST API multiple times to obtain a large number of records. The simplest and most popular way to achieve this is by using the offset method. However, this article introduces a method to obtain a large number of records from a Kintone App at a higher speed than the offset method. During testing, the written method successfully reduced the time to retrieve 500,000 records to 33% of the time taken by the offset method.
Method 1: Retrieving records with the offset method
The simple way to retrieve records in bulk is to increase the value of the offset gradually and run the Get Records REST API multiple times.
Issue with the offset method
All records can successfully be obtained with this method. However, when there are too many records, the time to complete the process slows down dramatically.
The chart above shows a measurement of the time required for retrieving all records as the number of records increases. Furthermore, the time taken to retrieve all records increases not linearly, but quadratically.
Method 2: Retrieving records with the seek method
Instead of using the offset method, the retrieval process can be sped up by retrieving records with the seek method.
This method adds the condition "record number > record number of the last record" when writing out the query, using the record number field value of the final record of the batch of records that were retrieved in the previous loop. The code retrieves the next 500 records because it retrieves records in bulk in the order of "order by record number ascending".
This technique is a commonly known method for relational database management systems (RDBMS) in general, and is not limited to Kintone. Refer to the external article "Paging through results" from the database performance website, Use the Index, Luke! for more information on the seek method.
The chart below shows a comparison of the time to complete the process using Methods 1 and 2.
The time required for retrieving record data is linearly proportional to the number of records when Method 2 is used.
The benefit of speed becomes more significant as the total number of records to retrieve increases. During testing, the time to output 500,000 records using Method 2 (the seek method) was almost 3 times faster compared to Method 1 (the offset method).
From this evidence, it's clear that Method 2 is more suitable than Method 1 for retrieving hundreds of thousands of records.
Issue with the seek method
There is a problem with Method 2 as well, which is the increased complexity of the code.
When outputting all records, the sort order did not matter too much, so the record number was used. However, if there is a need to output records based on the sort conditions of multiple fields, the query conditions of Method 2 will become complicated.
This article introduced a method to increase the speed of retrieving records from a Kintone App. However, the seek method also has the disadvantage of making the code more complicated. Make sure to consider both methods when retrieving a large number of records.