Description bridge issue in SQLite implementation https://jira.appcelerator.org/browse/TIMOB-12291

sqliteBridgeBottleneckTest.js

// https://jira.appcelerator.org/browse/TIMOB-12291

exports.compareQueries = function () {
    testCommonWay();
    testGroupConcat();
};

const TEST_QUERY_EXEC_COUNT = 50;

function testCommonWay() {
    const startTime = new Date();
    Ti.API.debug(`testCommonWay() start`);

    for (let i of Array.from({length: TEST_QUERY_EXEC_COUNT})) {
        selectCommonWay();
    }

    const endTime = new Date();
    Ti.API.debug(`testCommonWay() loop:${TEST_QUERY_EXEC_COUNT} complete in ${endTime.getTime() - startTime.getTime()} ms`);
};

function testGroupConcat() {
    const startTime = new Date();
    Ti.API.debug(`testGroupConcat() start`);

    for (let i of Array.from({length: TEST_QUERY_EXEC_COUNT})) {
        selectGroupConcat();
    }

    const endTime = new Date();
    Ti.API.debug(`testGroupConcat() loop:${TEST_QUERY_EXEC_COUNT} complete in ${endTime.getTime() - startTime.getTime()} ms`);
};

function selectCommonWay() {
    const dbName = 'storage';
    const db = Ti.Database.open(dbName);
    const commonSelectQuery = `
        SELECT _id, EquipmentType, DeviceType, ModelNumber, SerialNumber, InstallationDate, DeviceLocation, ExpirationType
        FROM devices
        LIMIT 1000
    `;

    let devices = [];

    const resultSet = db.execute(commonSelectQuery, [])
    if (resultSet) {
        while (resultSet.isValidRow()) {
            // just process result
            const device = {
                _id: resultSet.fieldByName('_id'),
                EquipmentType: resultSet.fieldByName('EquipmentType'),
                DeviceType: resultSet.fieldByName('DeviceType'),
                ModelNumber: resultSet.fieldByName('ModelNumber'),
                SerialNumber: resultSet.fieldByName('SerialNumber'),
                InstallationDate: resultSet.fieldByName('InstallationDate'),
                DeviceLocation: resultSet.fieldByName('DeviceLocation'),
                ExpirationType: resultSet.fieldByName('ExpirationType')
            };
            devices.push(device);

            resultSet.next();
        }
        resultSet.close();
    }

    db.close();

    // Ti.API.debug(`deviceCounter = ${devices.length}`);
}

function selectGroupConcat() {
    const dbName = 'storage';
    const db = Ti.Database.open(dbName);
    const groupConcatQuery = `
        SELECT  GROUP_CONCAT(
                    _id                             ||','||
                    COALESCE(EquipmentType, 0)      ||','||
                    COALESCE(DeviceType, 0)         ||','||
                    COALESCE(ModelNumber, 0)        ||','||
                    COALESCE(SerialNumber, 0)       ||','||
                    COALESCE(InstallationDate, 0)   ||','||
                    COALESCE(DeviceLocation, 0)     ||','||
                    COALESCE(ExpirationType, 0)
                ) as conc
        FROM (
            SELECT *
            FROM devices
            LIMIT 1000
        )
    `;

    let devices = [];

    const resultSet = db.execute(groupConcatQuery, []);
    if (resultSet) {
        while (resultSet.isValidRow()) {
            const concatenated = resultSet.fieldByName('conc');
            if (concatenated) {
                const concatenatedArray = concatenated.split(',');
                for (let j = 0; j < concatenatedArray.length; j = j + 8) {
                    const device = {
                        _id: concatenatedArray[j + 0],
                        EquipmentType: concatenatedArray[j + 1],
                        DeviceType: concatenatedArray[j + 2],
                        ModelNumber: concatenatedArray[j + 3],
                        SerialNumber: concatenatedArray[j + 4],
                        InstallationDate: concatenatedArray[j + 5],
                        DeviceLocation: concatenatedArray[j + 6],
                        ExpirationType: concatenatedArray[j + 7]
                    };
                    devices.push(device);
                }
            }

            resultSet.next();
        }

        resultSet.close();
    }

    db.close();

    // Ti.API.debug(`deviceCounter = ${devices.length}`);
}