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.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

function fetch(opt_offset, opt_records) {
  var offset = opt_offset || 0;
  var records = opt_records || [];
  var params = {
    app: kintone.app.getId(),
    query: 'order by $id asc limit 500 offset ' + offset
  };
  return kintone.api('/k/v1/records', 'GET', params).then(function(resp) {
    records = records.concat(resp.records);
    if (resp.records.length === 500) {
      /* If the maximum number of retrievable records was retrieved, there is a possibility that more records exist.
            Therefore the offset is set to the maximum record limit and the REST API to retrieve records is run again.*/
      return fetch(offset + 500, records);
    }
    return records;
  });
}

fetch().then(function(records) {
  console.log(records);
});

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.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

function fetch_fast(opt_last_record_id, opt_records) {
  var records = opt_records || [];
  var query = opt_last_record_id ? '$id > ' + opt_last_record_id : '';
  query += ' order by $id asc limit 500';
  var params = {
    app: kintone.app.getId(),
    query: query
  };
  return kintone.api('/k/v1/records', 'GET', params).then(function(resp) {
    records = records.concat(resp.records);
    if (resp.records.length === 500) {
      /* If the maximum number of retrievable records was retrieved, there is a possibility that more records exist.
        Therefore, the next 500 records that have a record number larger than the last retrieved record are retrieved.
        Since the records are retrieved all at once in ascending record number order,
        it is possible to retrieve the next 500 records with these conditions.
      */
      return fetch_fast(resp.records[resp.records.length - 1].$id.value, records);
    }
    return records;
  });
}

fetch_fast().then(function(records) {
  console.log(records);
});

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.