Get All Records from an App: The Offset Method vs The Seek Method
Overview
This article introduces how to retrieve data of all records from an App. The Offset Method and the Seek Method are two approaches to retrieving all records. This article introduces these methods, and compares their performance.
Retrieve Records with the Offset Method
How it works
The Get Records API is called recursively, using promises.
The API is called to retrieve a number of records equal to the value set in the limit parameter. The offset is then shifted, and the function calls itself again. This continues until all records are retrieved from the App.
Sample code
Navigate to an App, and open the browser's developer tools. Type the following code into the console and run it.
|
|
Data of all records from the currently viewed App should be output into the console. It may take a few seconds before the result is displayed.
Time taken for the data retrieval
All records can be obtained with this method. However, if 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. The time taken to retrieve all records increases not linearly, but quadratically.
Retrieve Records with the Seek Method
How it works
The Get Records API is called recursively, using promises.
This method adds the condition {record number} > {record number of the last record}
in the query. This code is able to retrieve the next batch of records, because it retrieves records 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.
Sample code
Navigate to an App, and open the browser's developer tools. Type the following code into the console and run it.
|
|
Data of all records from the currently viewed App should be output into the console. It may take a few seconds before the result is displayed.
Time taken for the data retrieval
The chart below shows a comparison of the time to complete the process using the Offset Method (Method 1) and the Seek Method (Method 2).
The time required for retrieving record data is linearly proportional to the number of records when the Seek Method 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 the Seek Method was almost 3 times faster compared to the Offset Method.
From this evidence, it is clear that the Seek Method is more suitable than the Offset Method for retrieving hundreds of thousands of records.
Although the Seek Method is fast, the complexity of the code increases. In this example, the order was sorted with the record number. If there is a need to output records based on the sort conditions of multiple fields, the Seek Method will become more complicated to implement.