Get All Records from an App: The Offset Method vs The Cursor API Method

Contents

Overview

This article introduces how to retrieve data of all records from an App. The Offset Method and the Cursor API Method are two approaches to retrieving all records. This article introduces these methods, and compares their performance.

The Cursor API Method

API Overview

There are 3 REST APIs in Kintone related to cursors.

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 a specific record's location. The cursors allow for retrieving records based on their position information.

How to Retrieve Records

Create a cursor using the Add Cursor API

A Database Cursor is created using the Add Cursor API. The query parameter defines the cursor's range.

Retrieve records using the Get Cursor API

Multiple records are retrieved by specifying the Cursor ID with the Get Cursor API.

The Get Cursor API is used, and the cursor position shifts by the size parameter specified in the Add Cursor API request. The records that were passed over by the cursor are then retrieved.

Repeat the Get Cursor API

The Get Cursor API is repeatedly used against the same cursor until all queried 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
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
// Create a cursor using Add Cursor API
var postCursor = function(_params) {
  var MAX_READ_LIMIT = 500;

  var params = _params || {};
  var app = params.app || kintone.app.getId();
  var filterCond = params.filterCond;
  var sortConds = params.sortConds;
  var fields = params.fields;

  var conditions = [];
  if (filterCond) {
    conditions.push(filterCond);
  }

  var sortCondsAndLimit =
    (sortConds && sortConds.length > 0 ? ' order by ' + sortConds.join(', ') : '');
  var query = conditions.join(' and ') + sortCondsAndLimit;
  var body = {
    app: app,
    query: query,
    size: MAX_READ_LIMIT
  };
  if (fields && fields.length > 0) {
    body.fields = fields;
  }

  return kintone.api(kintone.api.url('/k/v1/records/cursor.json', true), 'POST', body).then(function(r) {
    return r.id;
  });
};

// Retrieve records from the cursor using Get Cursor API
var getRecordsByCursorId = function(_params) {
  var params = _params || {};
  var id = params.id;

  var data = params.data;

  if (!data) {
    data = {
      records: []
    };
  }

  var body = {
    id: id
  };
  return kintone.api(kintone.api.url('/k/v1/records/cursor.json', true), 'GET', body).then(function(r) {
    data.records = data.records.concat(r.records);
    if (r.next) {
      return getRecordsByCursorId({id: id, data: data});
    }
    return data;
  });
};

/*
  * @param {Object} params
  *   - app {String}: APP ID (Default value is current app id)
  *   - filterCond {String}: Query condition
  *   - sortConds {Array}: Sorting conditions
  *   - fields {Array}: Fields to retrieve
  * @return {Object} response
  *   - records {Array}: Target records
  */
var getRecords = function(_params) {
  return postCursor(_params).then(function(id) {
    return getRecordsByCursorId({id: id});
  });
};

getRecords().then(function(data) {
  console.log(data);
});

Data of all records inside the App should be output into the console.

Performance of the Two Methods

Comparison overview

This section compares the performance of the Offset Method method and the Cursor API Method. The following graphs illustrate the time the two methods took retrieving records under various conditions. The time it took to create the cursor is also included.

Case 1: 100,000 records retrieved sorted by Record ID

The Cursor API method and the Offset Method take almost the same time to retrieve the records. The time increases linearly as the records to retrieve increase.

Case 2: 100,000 records retrieved sorted by Text field

The Cursor API Method takes a slightly longer time to retrieve records compared to Case 1. In comparison, the Offset Method notably takes more time. The slope for the Cursor API Method is very similar to Case 1, while the slope for the Offset Method is nearly 45-degrees.

Case 3: 500,000 records retrieved sorted by Record ID

Both the Cursor API and Offset Methods have a nearly 45-degree slope. The Cursor API Method is slightly faster at retrieving records.

Case 4: 500,000 records retrieved sorted by Text field

The difference in time between the Cursor API and Offset Methods grows greatly as the records to retrieve approaches 500,000. The slope for the Cursor API Method is relatively flat, while the slope for the Offset Method is nearly 45-degrees.

Conclusion

The graphs show that the Cursor API Method is consistently faster at retrieving records than the Offset Method. This difference in speed between the methods increases as more records are retrieved. The Offset Method's performance is inconsistent, with varied retrieval times depending on the sort method and the number of records.

Limitations