kintone レコード取得APIのパラレル処理

kintone で、数万件のレコード取得をAPI で行うと数分の処理時間を要します。
処理時間を短縮する手段として、APIのパラレル処理を検証してみました。
結果、約２万件のレコード取得で約４５秒の処理時間が、パラレル処理により約１１秒程度に短縮できました。

ある程度件数の多いレコード処理の場合、パラレル処理は有効な手段だと思われます。

kintone API仕様

同時に処理できるAPIは、１０件までという制約があります。

検証用 kintone アプリ概要

在庫と金額項目をもつアプリ。
レコード件数: 20,426 件

カスタム集計処理

レコード全件取得後、集計してグリッド表示

API 処理

処理概要
コードは、パラレル度５の処理

• ログ処理　API 処理時間確認用
• レコード全件取得 (getRecords)
  • 最初のレコード取得 (offset 0, totalCount 取得)
  • パラレル レコード取得処理 (fetchRecords)
• SQL集計処理
• グリッド表示

パラレル度の指定：opt_parallel
getRecords(appId, opt_query, opt_fields, opt_limit, opt_parallel)

////////////////////////////////////////////////////////////////////////////
// kintone JavaScript: Summary of kintone records by alasql.
//  2017.03.16 by rex0220
////////////////////////////////////////////////////////////////////////////
(function() {
    "use strict";

    // Log Output
    var Log = (function() {
        // コンストラクタ
        var Log = function(name, enabled) {
            if(!(this instanceof Log)) {
                return new Log(name);
            }
            this.name = name || 'log';
            this.enabled = enabled;
            this.start  = moment();
            this.prev = this.start;
            this.sno = 1;
            if (!this.enabled) return;
            console.log(this.name + ' start: ' + this.start.format('YYYY-MM-DD HH:mm:ss'));
        }

        var p = Log.prototype;

        // writeメソッド
        p.write = function(step, info) {
            if (!this.enabled) return;
            var m1 = moment();
            var d1 = m1.diff(this.prev, 'sss') / 1000;
            if (info)
                console.log(this.name + '-' + this.sno + ' ' + m1.format('HH:mm:ss')  + ' (' + d1 + ' 秒) : ' + step + ', ', info );
            else
                console.log(this.name + '-' + this.sno + ' ' + m1.format('HH:mm:ss')  + ' (' + d1 + ' 秒) : ' + step );
            this.prev = m1;
            this.sno++;
        }

        // infoメソッド
        p.info = function(step, info) {
            if (!this.enabled) return;
            console.log(this.name + '-' + this.sno + ' ' + step, info );
        }

        // endメソッド
        p.end = function() {
            if (!this.enabled) return;
            var m1 = moment();
            var d1 = m1.diff(this.start, 'sss') / 1000;
            console.log(this.name + ' end ( ' + this.sno + ' step ) : ' + m1.format('YYYY-MM-DD HH:mm:ss')  + ' (' + d1 + ' 秒)' );
        }

        return Log;
    })();


    // 一覧表示
    kintone.events.on("app.record.index.show", function(event) {
        if (event.viewId !== 5345322) return event;

        var log = new Log('SQL-CHECK', true);

        var obj = {};

        // 情報取得
        getRecords(event.appId, '', ['店舗', 'ミッション', '仕入合計', '販売予定粗利', '販売価格'], 500, 5).then(function(records) {
        // getRecords(event.appId, '').then(function(records) {

            log.write('get record', records.length + ' records.' );

            obj.rs1 = convertToRows(records);

            log.write('convert rows', obj.rs1.length + ' rows.' );

            // console.log('rs1', obj.rs1);

            // 情報を集計
            var result1 = 
                alasql(
                "SELECT a.[店舗], a.[ミッション], COUNT(*) as [件数], SUM(a.[仕入合計]) as [仕入合計], SUM(a.[販売予定粗利]) as [販売予定粗利], SUM(a.[販売価格])/COUNT(*) as [販売価格] \
                FROM ? AS a \
                GROUP BY a.[店舗], a.[ミッション] \
                ORDER BY a.[店舗], a.[ミッション]", [obj.rs1]);

            log.write('select group by', result1.length + ' rows.' );

            // grid 表示
            var grid = document.getElementById('xp-grid');
            new Handsontable(grid, { 
                data: result1, 
                colWidths: [100, 100, 100, 200, 200, 200],
                colHeaders: ['店舗', 'ミッション', '件数', '仕入合計', '販売予定粗利', '販売価格'], 
                columns: [
                    { data: '店舗' },
                    { data: 'ミッション' },
                    { data: '件数', type: 'numeric', format: '0,0' },
                    { data: '仕入合計', type: 'numeric', format: '0,0' },
                    { data: '販売予定粗利', type: 'numeric', format: '0,0' },
                    { data: '販売価格', type: 'numeric', format: '0,0' },
                ],
                readOnly: true 
            });

            log.write('grid display ');
            log.end();

        });

        return event;
    });

    // get records
    function getRecords(appId, opt_query, opt_fields, opt_limit, opt_parallel) {
        var glog = new Log('api-get', true);
        var query = opt_query || '';
        var offset = 0;
        var limit = opt_limit || 500;
        var allRecords = [];
        var parallel = opt_parallel || 3;
        var params = {app: appId, query: query + ' limit ' + limit + ' offset ' + offset, totalCount: true };
        if (opt_fields) params.fields = opt_fields;

        // first get
        return kintone.api(kintone.api.url('/k/v1/records', true), 'GET', params).then(function(resp) {
            glog.write('fetch records', resp.records.length + ' rows.' );
            allRecords = allRecords.concat(resp.records);
            // console.log('resp', resp);
            var pcnt = parseInt((Number(resp.totalCount) - 1) / limit);

            var parray = [];
            var arrayRecords = [];
            var pno = 0;
            for (var i = 0; i < parallel; i++) {
                var p1 = new Promise(function (resolve, reject) {
                    if (pno >= pcnt) {
                        resolve(true);
                    }
                    else {
                        var plog = new Log('api-p' + i, true);
                        fetchRecords(i, plog).then(function(x) {
                            plog.end();
                            resolve(true);
                        });
                    }
                });
                parray.push(p1);
            }

            console.log('parray', parray);
            return Promise.all(parray).then(function (value) {
                console.log('Promise.all', value);
                for (var i = 0; i < pcnt; i++) {
                   allRecords = allRecords.concat(arrayRecords[i]);
                }
                glog.end();
                return allRecords;
            });

            // parallel get
            function fetchRecords(procInstantNo, plog) {
                var curpno = pno;
                pno++;
                var offset = limit * (curpno + 1);
                var params = {app: appId, query: query + ' limit ' + limit + ' offset ' + offset };
                if (opt_fields) params.fields = opt_fields;
                // console.log('params:' + procInstantNo, params);
                return kintone.api(kintone.api.url('/k/v1/records', true), 'GET', params).then(function(resp2) {
                    plog.write('fetch records', resp2.records.length + ' rows.'  + ' offset ' + offset );
                    // console.log('resp2', resp2);
                    arrayRecords[curpno] = resp2.records;
                    // console.log('next pno:' + procInstantNo, pno);
                    if (pno >= pcnt) {
                        return true;
                    }
                    return fetchRecords(procInstantNo, plog);
                });
            }

        });
    }

    // records convert to table rows 
    function convertToRows(records) {
        var rows = records.map(function(record){
            var keys = Object.keys(record);
            var row = {};
            keys.map(function(key){
                row[key] = record[key].type === 'NUMBER' ? Number(record[key].value) : record[key].value;
            });
            return row;
        });
        return rows;
    }

})();

ログ

20,476 件のレコードの場合、レコード取得API は、４１回の発行となります。

パラレル度１

パラレル度１は、通常の順番に処理するのと同等です。
約４５秒の処理時間です。

SQL-CHECK start: 2017-03-16 16:06:01
api-get start: 2017-03-16 16:06:01
api-get-1 16:06:02 (1.141 秒) : fetch records,  500 rows.
api-p0 start: 2017-03-16 16:06:02
parray [Promise]
api-p0-1 16:06:03 (1.108 秒) : fetch records,  500 rows. offset 500
api-p0-2 16:06:04 (1.103 秒) : fetch records,  500 rows. offset 1000
api-p0-3 16:06:05 (0.974 秒) : fetch records,  500 rows. offset 1500
api-p0-4 16:06:06 (1.139 秒) : fetch records,  500 rows. offset 2000
api-p0-5 16:06:07 (1.025 秒) : fetch records,  500 rows. offset 2500
api-p0-6 16:06:08 (1.111 秒) : fetch records,  500 rows. offset 3000
api-p0-7 16:06:09 (1.029 秒) : fetch records,  500 rows. offset 3500
api-p0-8 16:06:10 (1.134 秒) : fetch records,  500 rows. offset 4000
api-p0-9 16:06:12 (1.085 秒) : fetch records,  500 rows. offset 4500
api-p0-10 16:06:13 (1.069 秒) : fetch records,  500 rows. offset 5000
api-p0-11 16:06:14 (1.009 秒) : fetch records,  500 rows. offset 5500
api-p0-12 16:06:15 (1.124 秒) : fetch records,  500 rows. offset 6000
api-p0-13 16:06:16 (1.087 秒) : fetch records,  500 rows. offset 6500
api-p0-14 16:06:17 (1.111 秒) : fetch records,  500 rows. offset 7000
api-p0-15 16:06:18 (0.962 秒) : fetch records,  500 rows. offset 7500
api-p0-16 16:06:19 (1.127 秒) : fetch records,  500 rows. offset 8000
api-p0-17 16:06:20 (1.054 秒) : fetch records,  500 rows. offset 8500
api-p0-18 16:06:21 (1.117 秒) : fetch records,  500 rows. offset 9000
api-p0-19 16:06:22 (1.062 秒) : fetch records,  500 rows. offset 9500
api-p0-20 16:06:23 (1.118 秒) : fetch records,  500 rows. offset 10000
api-p0-21 16:06:24 (1.024 秒) : fetch records,  500 rows. offset 10500
api-p0-22 16:06:26 (1.157 秒) : fetch records,  500 rows. offset 11000
api-p0-23 16:06:27 (1.095 秒) : fetch records,  500 rows. offset 11500
api-p0-24 16:06:28 (1.018 秒) : fetch records,  500 rows. offset 12000
api-p0-25 16:06:29 (1.171 秒) : fetch records,  500 rows. offset 12500
api-p0-26 16:06:30 (1.121 秒) : fetch records,  500 rows. offset 13000
api-p0-27 16:06:31 (1.034 秒) : fetch records,  500 rows. offset 13500
api-p0-28 16:06:32 (1.155 秒) : fetch records,  500 rows. offset 14000
api-p0-29 16:06:33 (1.169 秒) : fetch records,  500 rows. offset 14500
api-p0-30 16:06:34 (1.051 秒) : fetch records,  500 rows. offset 15000
api-p0-31 16:06:35 (1.054 秒) : fetch records,  500 rows. offset 15500
api-p0-32 16:06:37 (1.276 秒) : fetch records,  500 rows. offset 16000
api-p0-33 16:06:38 (1.116 秒) : fetch records,  500 rows. offset 16500
api-p0-34 16:06:39 (1.179 秒) : fetch records,  500 rows. offset 17000
api-p0-35 16:06:40 (1.2 秒) : fetch records,  500 rows. offset 17500
api-p0-36 16:06:41 (1.176 秒) : fetch records,  500 rows. offset 18000
api-p0-37 16:06:42 (1.057 秒) : fetch records,  500 rows. offset 18500
api-p0-38 16:06:43 (1.018 秒) : fetch records,  500 rows. offset 19000
api-p0-39 16:06:45 (1.261 秒) : fetch records,  500 rows. offset 19500
api-p0-40 16:06:46 (0.979 秒) : fetch records,  426 rows. offset 20000
api-p0 end ( 41 step ) : 2017-03-16 16:06:46 (43.86 秒)
Promise.all [true]
api-get end ( 2 step ) : 2017-03-16 16:06:46 (45.008 秒)
SQL-CHECK-1 16:06:46 (45.012 秒) : get record,  20426 records.
SQL-CHECK-2 16:06:46 (0.077 秒) : convert rows,  20426 rows.
SQL-CHECK-3 16:06:46 (0.077 秒) : select group by,  3 rows.
SQL-CHECK-4 16:06:46 (0.062 秒) : grid display 
SQL-CHECK end ( 5 step ) : 2017-03-16 16:06:46 (45.229 秒)

パラレル度５

パラレル処理は、 api-p0, api-p1, api-p2, api-p3, api-p4 で記録されます。
ログのoffset の値により、API の処理時間で順番が逆転しているのが分かります。

SQL-CHECK start: 2017-03-16 15:50:04
api-get start: 2017-03-16 15:50:04
api-get-1 15:50:05 (1.106 秒) : fetch records,  500 rows.
api-p0 start: 2017-03-16 15:50:05
api-p1 start: 2017-03-16 15:50:05
api-p2 start: 2017-03-16 15:50:05
api-p3 start: 2017-03-16 15:50:05
api-p4 start: 2017-03-16 15:50:05
 parray [Promise, Promise, Promise, Promise, Promise]
api-p2-1 15:50:06 (1.109 秒) : fetch records,  500 rows. offset 1500
api-p1-1 15:50:06 (1.126 秒) : fetch records,  500 rows. offset 1000
api-p4-1 15:50:06 (1.136 秒) : fetch records,  500 rows. offset 2500
api-p0-1 15:50:06 (1.227 秒) : fetch records,  500 rows. offset 500
api-p3-1 15:50:06 (1.251 秒) : fetch records,  500 rows. offset 2000
api-p2-2 15:50:07 (1.126 秒) : fetch records,  500 rows. offset 3000
api-p4-2 15:50:08 (1.184 秒) : fetch records,  500 rows. offset 4000
api-p1-2 15:50:08 (1.218 秒) : fetch records,  500 rows. offset 3500
api-p3-2 15:50:08 (1.2 秒) : fetch records,  500 rows. offset 5000
api-p0-2 15:50:08 (1.247 秒) : fetch records,  500 rows. offset 4500
api-p2-3 15:50:09 (1.136 秒) : fetch records,  500 rows. offset 5500
api-p3-3 15:50:09 (0.97 秒) : fetch records,  500 rows. offset 7000
api-p4-3 15:50:09 (1.165 秒) : fetch records,  500 rows. offset 6000
api-p1-3 15:50:09 (1.232 秒) : fetch records,  500 rows. offset 6500
api-p0-3 15:50:09 (1.259 秒) : fetch records,  500 rows. offset 7500
api-p2-4 15:50:10 (1.219 秒) : fetch records,  500 rows. offset 8000
api-p3-4 15:50:10 (1.183 秒) : fetch records,  500 rows. offset 8500
api-p4-4 15:50:10 (1.157 秒) : fetch records,  500 rows. offset 9000
api-p1-4 15:50:10 (1.156 秒) : fetch records,  500 rows. offset 9500
api-p0-4 15:50:10 (1.17 秒) : fetch records,  500 rows. offset 10000
api-p2-5 15:50:11 (1.168 秒) : fetch records,  500 rows. offset 10500
api-p3-5 15:50:11 (1.224 秒) : fetch records,  500 rows. offset 11000
api-p1-5 15:50:11 (1.214 秒) : fetch records,  500 rows. offset 12000
api-p4-5 15:50:11 (1.351 秒) : fetch records,  500 rows. offset 11500
api-p0-5 15:50:11 (1.193 秒) : fetch records,  500 rows. offset 12500
api-p2-6 15:50:12 (1.177 秒) : fetch records,  500 rows. offset 13000
api-p3-6 15:50:12 (1.117 秒) : fetch records,  500 rows. offset 13500
api-p1-6 15:50:12 (1.163 秒) : fetch records,  500 rows. offset 14000
api-p4-6 15:50:12 (1.127 秒) : fetch records,  500 rows. offset 14500
api-p0-6 15:50:12 (1.146 秒) : fetch records,  500 rows. offset 15000
api-p3-7 15:50:13 (1.052 秒) : fetch records,  500 rows. offset 16000
api-p2-7 15:50:13 (1.273 秒) : fetch records,  500 rows. offset 15500
api-p4-7 15:50:14 (1.236 秒) : fetch records,  500 rows. offset 17000
api-p1-7 15:50:14 (1.28 秒) : fetch records,  500 rows. offset 16500
api-p0-7 15:50:14 (1.237 秒) : fetch records,  500 rows. offset 17500
api-p3-8 15:50:14 (1.163 秒) : fetch records,  500 rows. offset 18000
api-p3 end ( 9 step ) : 2017-03-16 15:50:14 (9.161 秒)
api-p2-8 15:50:15 (1.135 秒) : fetch records,  500 rows. offset 18500
api-p2 end ( 9 step ) : 2017-03-16 15:50:15 (9.344 秒)
api-p0-8 15:50:15 (0.92 秒) : fetch records,  426 rows. offset 20000
api-p0 end ( 9 step ) : 2017-03-16 15:50:15 (9.399 秒)
api-p1-8 15:50:15 (1.206 秒) : fetch records,  500 rows. offset 19500
api-p1 end ( 9 step ) : 2017-03-16 15:50:15 (9.596 秒)
api-p4-8 15:50:15 (1.269 秒) : fetch records,  500 rows. offset 19000
api-p4 end ( 9 step ) : 2017-03-16 15:50:15 (9.625 秒)
 Promise.all [true, true, true, true, true]
api-get end ( 2 step ) : 2017-03-16 15:50:15 (10.753 秒)
SQL-CHECK-1 15:50:15 (10.759 秒) : get record,  20426 records.
SQL-CHECK-2 15:50:15 (0.06 秒) : convert rows,  20426 rows.
SQL-CHECK-3 15:50:15 (0.069 秒) : select group by,  3 rows.
SQL-CHECK-4 15:50:15 (0.056 秒) : grid display 
SQL-CHECK end ( 5 step ) : 2017-03-16 15:50:15 (10.945 秒)

パラレル度１０

API 仕様による max 10 のパラレル度です。
処理時間が約１１秒とパラレル度５の場合とほとんど変わりません。

APIの処理時間をみると、２秒以上かかっているものもあり、何かボトルネックがありそうです。

SQL-CHECK start: 2017-03-16 16:17:03
api-get start: 2017-03-16 16:17:03
api-get-1 16:17:04 (1.362 秒) : fetch records,  500 rows.
api-p0 start: 2017-03-16 16:17:04
api-p1 start: 2017-03-16 16:17:04
api-p2 start: 2017-03-16 16:17:04
api-p3 start: 2017-03-16 16:17:04
api-p4 start: 2017-03-16 16:17:04
api-p5 start: 2017-03-16 16:17:04
api-p6 start: 2017-03-16 16:17:04
api-p7 start: 2017-03-16 16:17:04
api-p8 start: 2017-03-16 16:17:04
api-p9 start: 2017-03-16 16:17:04
parray [Promise, Promise, Promise, Promise, Promise, Promise, Promise, Promise, Promise, 
api-p0-1 16:17:06 (1.279 秒) : fetch records,  500 rows. offset 500
api-p4-1 16:17:06 (1.307 秒) : fetch records,  500 rows. offset 2500
api-p2-1 16:17:06 (1.324 秒) : fetch records,  500 rows. offset 1500
api-p1-1 16:17:06 (1.357 秒) : fetch records,  500 rows. offset 1000
api-p3-1 16:17:06 (1.4 秒) : fetch records,  500 rows. offset 2000
api-p5-1 16:17:06 (1.409 秒) : fetch records,  500 rows. offset 3000
api-p7-1 16:17:07 (2.517 秒) : fetch records,  500 rows. offset 4000
api-p8-1 16:17:07 (2.548 秒) : fetch records,  500 rows. offset 4500
api-p0-2 16:17:07 (1.354 秒) : fetch records,  500 rows. offset 5500
api-p6-1 16:17:07 (2.636 秒) : fetch records,  500 rows. offset 3500
api-p9-1 16:17:07 (2.649 秒) : fetch records,  500 rows. offset 5000
api-p4-2 16:17:07 (1.383 秒) : fetch records,  500 rows. offset 6000
api-p5-2 16:17:08 (2.457 秒) : fetch records,  500 rows. offset 8000
api-p2-2 16:17:08 (2.588 秒) : fetch records,  500 rows. offset 6500
api-p8-2 16:17:08 (1.414 秒) : fetch records,  500 rows. offset 9000
api-p3-2 16:17:08 (2.618 秒) : fetch records,  500 rows. offset 7500
api-p7-2 16:17:08 (1.568 秒) : fetch records,  500 rows. offset 8500
api-p1-2 16:17:09 (2.837 秒) : fetch records,  500 rows. offset 7000
api-p6-2 16:17:10 (2.537 秒) : fetch records,  500 rows. offset 10000
api-p9-2 16:17:10 (2.642 秒) : fetch records,  500 rows. offset 10500
api-p0-3 16:17:10 (2.692 秒) : fetch records,  500 rows. offset 9500
api-p5-3 16:17:10 (1.545 秒) : fetch records,  500 rows. offset 11500
api-p4-3 16:17:10 (2.741 秒) : fetch records,  500 rows. offset 11000
api-p2-3 16:17:10 (1.617 秒) : fetch records,  500 rows. offset 12000
api-p8-3 16:17:11 (2.436 秒) : fetch records,  500 rows. offset 12500
api-p3-3 16:17:11 (2.53 秒) : fetch records,  500 rows. offset 13000
api-p7-3 16:17:11 (2.497 秒) : fetch records,  500 rows. offset 13500
api-p6-3 16:17:11 (1.431 秒) : fetch records,  500 rows. offset 14500
api-p9-3 16:17:11 (1.457 秒) : fetch records,  500 rows. offset 15000
api-p1-3 16:17:11 (2.59 秒) : fetch records,  500 rows. offset 14000
api-p0-4 16:17:12 (2.324 秒) : fetch records,  500 rows. offset 15500
api-p0 end ( 5 step ) : 2017-03-16 16:17:12 (7.65 秒)
api-p2-4 16:17:12 (2.149 秒) : fetch records,  500 rows. offset 17000
api-p2 end ( 5 step ) : 2017-03-16 16:17:12 (7.679 秒)
api-p5-4 16:17:12 (2.382 秒) : fetch records,  500 rows. offset 16000
api-p5 end ( 5 step ) : 2017-03-16 16:17:12 (7.793 秒)
api-p4-4 16:17:12 (2.389 秒) : fetch records,  500 rows. offset 16500
api-p4 end ( 5 step ) : 2017-03-16 16:17:12 (7.821 秒)
api-p3-4 16:17:12 (1.321 秒) : fetch records,  500 rows. offset 18000
api-p3 end ( 5 step ) : 2017-03-16 16:17:12 (7.87 秒)
api-p8-4 16:17:12 (1.654 秒) : fetch records,  500 rows. offset 17500
api-p8 end ( 5 step ) : 2017-03-16 16:17:12 (8.052 秒)
api-p6-4 16:17:13 (2.173 秒) : fetch records,  500 rows. offset 19000
api-p6 end ( 5 step ) : 2017-03-16 16:17:13 (8.778 秒)
api-p1-4 16:17:13 (2.017 秒) : fetch records,  426 rows. offset 20000
api-p1 end ( 5 step ) : 2017-03-16 16:17:13 (8.801 秒)
api-p7-4 16:17:13 (2.266 秒) : fetch records,  500 rows. offset 18500
api-p7 end ( 5 step ) : 2017-03-16 16:17:13 (8.848 秒)
api-p9-4 16:17:13 (2.252 秒) : fetch records,  500 rows. offset 19500
api-p9 end ( 5 step ) : 2017-03-16 16:17:13 (9.001 秒)
Promise.all [true, true, true, true, true, true, true, true, true, true]
api-get end ( 2 step ) : 2017-03-16 16:17:13 (10.386 秒)
SQL-CHECK-1 16:17:13 (10.394 秒) : get record,  20426 records.
SQL-CHECK-2 16:17:13 (0.069 秒) : convert rows,  20426 rows.
SQL-CHECK-3 16:17:13 (0.057 秒) : select group by,  3 rows.
SQL-CHECK-4 16:17:14 (0.046 秒) : grid display 
SQL-CHECK end ( 5 step ) : 2017-03-16 16:17:14 (10.566 秒)

同時接続数　６の制約

network の処理状況を確認すると、APIの回答が来ても、処理待ちになっているものがあります。

chrome の場合、同時接続数６という制約があり、それ以上は待ちになるようです。
ブラウザー毎に、同時接続数は異なるようです。

各ブラウザの同時接続数を変更する方法

まとめ

レコード件数 20,426 件の処理時間

パラレル度	処理時間
1	45 秒
5	11 秒
10	11 秒

レコード取得のパラレル処理は、件数が多い場合は有効。
ブラウザの同時接続数６の制約と、複数PCからのAPI実行も考慮すると、
パラレル度は多くても５までに抑えたほうがよさそうです。