Skip to content

Instantly share code, notes, and snippets.

@m1ga

m1ga/sqliteBridgeBottleneckTest.js

Forked from zo0m/sqliteBridgeBottleneckTest.js
Created April 3, 2020 14:51
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save m1ga/a66acbbdd9b3570dde17e21aa178f8f9 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save m1ga/a66acbbdd9b3570dde17e21aa178f8f9 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. @zo0m zo0m created this gist Apr 3, 2020.
    124 changes: 124 additions & 0 deletions sqliteBridgeBottleneckTest.js
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,124 @@
    // 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}`);
    }