kintone でSQLを使う（その２）パフォーマンス検証

kintone でSQLを使う の続き。
対象レコードを通常使われる程度に増やして、パフォーマンス検証してみました。

結果は、レコード取得がほとんどの処理時間で、集計・表示は問題なし。

集計元アプリ　

それなりに数値の件数があるものということで、市区町村別人口データを使います。

集計結果

必要ライブラリ

• alasql.min.js : JavaScript で SQL が使えます。
• handsontable : グリッド表示用

集計コード

処理概要

• 人口情報アプリのレコードを取得
• json 構造を簡単化
• SQL で集計（Group By）
• ログ処理

////////////////////////////////////////////////////////////////////////////
// kintone JavaScript: Summary of kintone records by alasql.
//  2017.02.15 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  : ' + m1.format('YYYY-MM-DD HH:mm:ss')  + ' (' + d1 + ' 秒)' );
        }

        return Log;
    })();


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

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

        var obj = {};

        // 人口情報取得
        fetchRecords(event.appId, '市など区分 in ("1","2","3")', ['都道府県コード', '都道府県名', '総数', '総数_男', '総数_女']).then(function(records) {

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

            obj.rs1 = convertToRows(records);

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


            // 顧客情報と注文情報を集計
            var result1 = 
                alasql(
                "SELECT a.[都道府県コード], a.[都道府県名], SUM(a.[総数]) as [総数], SUM(a.[総数_男]) as [総数_男], SUM(a.[総数_女]) 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: [50, 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' },
                ],
                readOnly: true 
            });

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

        });

        return event;
    });


    // get records
    function fetchRecords(appId, opt_query, opt_fields, opt_offset, opt_limit, opt_records) {
        var query = opt_query || '';
        var offset = opt_offset || 0;
        var limit = opt_limit || 500;
        var allRecords = opt_records || [];
        var params = {app: appId, query: query + ' limit ' + limit + ' offset ' + offset };
        if (opt_fields) params.fields = opt_fields;
        return kintone.api(kintone.api.url('/k/v1/records', true), 'GET', params).then(function(resp) {
            allRecords = allRecords.concat(resp.records);
            if (resp.records.length === limit) {
                return fetchRecords(appId, query, opt_fields, offset + limit, limit, allRecords);
            }
            return allRecords;
        });
    }

    // 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;
    }

})();

実行時間

約５秒の処理時間のほとんどがレコード取得にかかっており、SQL 処理は問題ない。

SQL-CHECK start: 2017-02-15 13:59:20
SQL-CHECK-1 13:59:24 (4.568 秒) : get record,  1719 records.
SQL-CHECK-2 13:59:24 (0.007 秒) : convert rows,  1719 rows.
SQL-CHECK-3 13:59:24 (0.03 秒) : select group by,  47 rows.
SQL-CHECK-4 13:59:24 (0.067 秒) : grid display 
SQL-CHECK end  : 2017-02-15 13:59:24 (4.673 秒)

対象レコードの抽出条件を変えて、件数を絞った場合。
約1,000 件のデータで、約３秒。この程度なら、運用でも問題ないと思われる。

SQL-CHECK start: 2017-02-15 14:08:16
SQL-CHECK-1 14:08:19 (2.832 秒) : get record,  1058 records.
SQL-CHECK-2 14:08:19 (0.005 秒) : convert rows,  1058 rows.
SQL-CHECK-3 14:08:19 (0.035 秒) : select group by,  25 rows.
SQL-CHECK-4 14:08:19 (0.062 秒) : grid display 
SQL-CHECK end  : 2017-02-15 14:08:19 (2.934 秒)

あとがき

SQL 実行のパフォーマンスについては、kintone の 1,000 レコード程度なら問題なし。
複数アプリのレコードを Join するしくみなどでも、レコード取得の処理が課題になる。